PDA

View Full Version : vlookup, find and relace the column headers



sindhuja
03-26-2012, 12:25 PM
Hi,

I need to use the vlookup function using macro since the excel file is very large.

I have the column P in sheet 1 to be compared with column A of sheet 2. The results should be in column Q of sheet 1 (insert a new column and name it results).

Once this is done, i need to find and replace the column names in row 2 of sheet1. columns names to be replaced and the value to be replaced are in the sheet named rearrange.

Can someone help me on this urgent requirement pls. . Also it takes long time to run a macro.. is there a way to speed up the time.

-Sindhuja

Bob Phillips
03-26-2012, 12:46 PM
There is a specific question in there?

sindhuja
03-26-2012, 08:46 PM
i need to perform vlookup first, then filter column by #NA and delete all the entries. Then i need to change the column heading as per specified in the rearrange sheet.

I have attached the sample sheet for further reference. I tried with the below coding not expected results not found. can you pls assist.


Sub PlaceVlookup()
Dim lastrw As Long
lastrw = Sheets(1).Range("P" & Rows.Count).End(xlUp).Row

'Sheets(1).Range("P1:P" & lastrw).NumberFormat = "Number"

Sheets(1).Range("Q1:Q" & lastrw).FormulaR1C1 = _
"=VLOOKUP(RC[1],Sheet2!RC:R[" & lastrw - 1 & "]B[1],1,0)"
''Copy/PasteSpecial Values
With Sheets(1).Range("A1:A" & lastrw)
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

End Sub



-Sindhuja

Bob Phillips
03-27-2012, 01:09 AM
Change the formula to

Sheets(1).Range("Q2:Q" & lastrw).FormulaR1C1 = _
"=VLOOKUP(RC[-1],Sheet2!C1,1,0)"

sindhuja
03-28-2012, 02:36 AM
Can you please help me with find and replace pls..