Microsoft Office excel 2007 - upgrade

I have been searching for hours for a solution that will work. Hoping some one here can help me. I need a macro solution to compare data in Sheet1, column D and compare that to Sheet2 column A. Where they match, I want to have the value in Sheet2 column C to be copied to Sheet 1 column A.

Thanks!

It doesn't sound like you need a macro. Have you tried VLOOKUP?

In Sheet1 Column A you would use something like this:

e.g. Sheet1!A1:

=VLOOKUP(D1, Sheet2!$A$1:$C$100, 3, 0)

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

True - I can do this using Vlook-up. Problem is that the data on Sheet 1 will get refreshed daily, so I would have to build and copy the formula for the hundreds of rows in the table every time the data is refreshed as it method for refresh would clear the entire sheet. My preference is to do this as a macro...unless I can't.

Well, in that case, we could write a macro to loop through Sheet1 Column D and find the data in Sheet2 Column A and move over to Column C and copy the data back to Sheet1, or... We could write a macro to place the VLOOKUP formula in Sheet 1 Column A.

Sub PlaceVlookup() Sheets(1).Range("A1:A100").FormulaR1C1 = _ "=VLOOKUP(RC[3],Sheet2!RC:R[99]C[2],3,0)" End Sub

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Yeah....we could do that...of course your example has a fixed range, I'd have to substitute that and have a variable range since the data is always changing... I have concerns with having a formula in the worksheet...based on nothing concrete other than knowing my audience and how they like to manipulate the spreadsheets...I was trying to keep it simple so the executives could have there way with the document. I've been bitten before by them moving columns adding columns and the like. I was thinking this would be cleaner, then I could also get rid of sheet2 when I sent it out. Again - so as not to confuse or tempt those receiving the data to make my life a nightmare. :-)

And maybe it's too difficult to do that...and I should just go and the formula...then I can do a copy and paste special, values only to accomplish what I was looking to do.

- Variable range based on Sheet1 Column D

- VLOOKUP used to get data

- VLOOKUP formulas replaced with values

- Sheet 2 deleted.Sub PlaceVlookup() 'Determine last row based on Column D lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row 'Insert formula using lastRw variable Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _ "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)" 'Copy/PasteSpecial Values With Sheets(1).Range("A1:A" & lastRw) .Copy .PasteSpecial Paste:=xlPasteValues End With 'Delete Sheet2 without warning Application.DisplayAlerts = False Sheets(2).Delete Application.DisplayAlerts = True End Sub

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Thanks for the help...but that didn't work. All it displayed in column A of Sheet 1 was the formula =VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0) When I selected one of the cells and hit F2 and enter, it gave me an error...formula you typed contains an error. After pressing ok it highlights the RC[3].

You said something about the data being refreshed. How is that done? Many methods of refreshing worksheets force the cell format to be Text. Excel has a nasty habit of retaining that Text format even when you rather it wouldn't. Try this version. It formats the Column A range as General before placing the formula in the cell.

Sub PlaceVlookup() 'Determine last row based on Column D lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row 'Format Column A range as General Sheets(1).Range("A1:A" & lastRw).NumberFormat = "General" 'Insert formula using lastRw variable Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _ "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)" 'Copy/PasteSpecial Values With Sheets(1).Range("A1:A" & lastRw) .Copy .PasteSpecial Paste:=xlPasteValues End With 'Delete Sheet2 without warning Application.DisplayAlerts = False Sheets(2).Delete Application.DisplayAlerts = True End Sub

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Thank You so much for your help! It works!!!

So I began looking through the hundreds of lines of data this week and discovered that the formula/code you helped me with is not working 100%. for one particular value I was expecting to get 20 matches. Instead I only recieved 9. When I added a new column to sheet1 and physically typed in a a vlookup formula I was able to get 19 matches. I think there is one data issue. Any thoughts on why it's not working? Does it matter that neither sheet1 nor sheet2 is sorted in any particular order?

Since I can't see your workbook from where I'm sitting, it's kind of hard for me to tell you why you aren't getting the results you expect. All my code does is put VLOOKUP formulas in a column, then Copy/Paste Values over them.

You could try this:

This is the same code with the sections that Paste Values and deletes Sheet 2 commented out.

When it's done, you'll be left with the formulas. Compare your formulas to mine and see what's different.

Sub PlaceVlookup() 'Determine last row based on Column D lastRw = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row 'Format Column A range as General Sheets(1).Range("A1:A" & lastRw).NumberFormat = "General" 'Insert formula using lastRw variable Sheets(1).Range("A1:A" & lastRw).FormulaR1C1 = _ "=VLOOKUP(RC[3],Sheet2!RC:R[" & lastRw - 1 & "]C[2],3,0)" ''Copy/PasteSpecial Values ' With Sheets(1).Range("A1:A" & lastRw) ' .Copy ' .PasteSpecial Paste:=xlPasteValues ' End With ''Delete Sheet2 without warning ' Application.DisplayAlerts = False ' Sheets(2).Delete ' Application.DisplayAlerts = True End Sub

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Thanks for the help. I actually figured out another way to get the same results by tweaking some of your code. Thank you for all the help!

Ask Your Question

Weekly Poll