Macro for vlookup

[Alka Bajaj]

Hello Expert,

Currently I am using vlookup between two sheets to get the desired output.

Steps Executed
1.Insert a column in sheet1 to put vlookup value
2.Perform vlookup function by refering sheet2

I need to automate the above steps using Macro. Please advise.
Appreciate your guidance.

Many Thanks.

Regards,
Alka

 

[John Davis]

Hello Expert,

Currently I am using vlookup between two sheets to get the desired output.

Steps Executed
1.Insert a column in sheet1 to put vlookup value
2.Perform vlookup function by refering sheet2

I need to automate the above steps using Macro. Please advise.
Appreciate your guidance.

Many Thanks.

Regards,
Alka

Without additional information (ie which column to insert, what data to pull, where the data is in Sheet2), this may get you started or give you some ideas. Modify as needed.

Sub AlkaBajaj()
Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row

Columns("B:B").Insert Shift:=xlToRight

With Range("B2:B" & lr)

    .Formula = "=VLOOKUP(A2,Sheet2!$A$2:$C$300,3,FALSE)"
    .Value = .Value

End With


End Sub

 

[Alka Bajaj]

Hi John,

Thanks for the input.
I tried the code with the modification as required.

Sub VLOOKUP()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(3).Row
Columns("H:H").Insert Shift:=xlToRight
With Range("H2:H" & lr)
.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Values
End With
End Sub


But getting the error in the highlighted line. Please help.

Many thanks in advance.

Regards,
Alka

 

[rberdakh]

Hi, there

I try to reply for John:

you should write: .Value = .Value

not write: .Value = .Values

All the best

 

[Alka Bajaj]

Thanks so much. It works!

Can I check how to provide name to the inserted coloumn using macro.

 

[John Davis]

Hi John,

Thanks for the input.
I tried the code with the modification as required.

Sub VLOOKUP()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(3).Row
Columns("H:H").Insert Shift:=xlToRight
With Range("H2:H" & lr)
.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Values
End With
End Sub


But getting the error in the highlighted line. Please help.

Many thanks in advance.

Regards,
Alka

It's .value = .value

Not .value = .values

 

[John Davis]

Thanks so much. It works!

Can I check how to provide name to the inserted coloumn using macro.

Do you mean a Header Title?

If so then:

Sub VLOOKUP()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(3).Row

Columns("H:H").Insert Shift:=xlToRight

With Range("H2:H" & lr)

.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Values

End With

Range("H1") = "YOUR HEADER TITLE"

End Sub

 

[Alka Bajaj]

I am trying to run the below code, earlier it was working fine , but now vlookup is not able to save any value. It performs functionality but then also the values gets automatically removed.

PLease appreciate your guidance.

Code used.
Sub lookup()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(3).Row
Columns("H:H").Insert Shift:=xlToRight
With Range("H2:H1000" & lr)
.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Value
End With
Range("H1") = "YOUR HEADER TITLE"

End Sub

 

[John Davis]

I am trying to run the below code, earlier it was working fine , but now vlookup is not able to save any value. It performs functionality but then also the values gets automatically removed.

PLease appreciate your guidance.

Code used.
Sub lookup()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(3).Row
Columns("H:H").Insert Shift:=xlToRight
With Range("H2:H1000" & lr)
.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Value
End With
Range("H1") = "YOUR HEADER TITLE"

End Sub

Try making this change (if you have a known range)

Sub lookup()

Columns("H:H").Insert Shift:=xlToRight
With Range("H2:H1000" )
.Formula = "=VLOOKUP(G2,ZDT_HR_CON_PERN!$I$2:$AG$50000,25,FALSE)"
.Value = .Value
End With
Range("H1") = "YOUR HEADER TITLE"

End Sub

 

[Alka Bajaj]

Hi John,

Tried below mentioned code. But it is not doing vlookup. It is just creating a new column with a header,