Vlookup Macro

[lakshmipathi123]

Hi All,

I have query regarding vlookup macro. I have recorded a macro for vlookup and its working for selected data (100 columns only) but if i add/delete few rows/columns macro not working for add/delete rows/columns.

I need a vba code which should select based data availability. Not only vlookup, anyother formula if i use it should select 1st row to last row (only data available cells).

Appreciate your help on this

Thank you in advance
Lakshmipathi+

 

[Domski]

Can we see your code?

Dom

 

[lakshmipathi123]

Can we see your code?

Dom

Hi Domski,

Thanks for your prompt reply . Please find below 2 formula codes i used in both i need macro to calculate data range irrespective recorded range K40227. Macro should find last row where the data availability.

EOMONTH CODE

Range("K2").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(RC[-1],RC[2]-1)"
Range("K2").Select
Selection.Copy
Range("J2").Select
Selection.End(xlDown).Select
Range("K40228").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("K40227").Select
Selection.End(xlUp).Select

VLOOKUP CODE

Columns("Y:Y").Select
Selection.Insert Shift:=xlToRight
Range("Y1").Select
ActiveCell.FormulaR1C1 = "BU"
Range("Y2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],BU!C[-22]:C[-16],7,0)"
Range("Y2").Select
Selection.Copy
Range("X2").Select
Selection.End(xlDown).Select
Range("Y40228").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-12
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("Y40227").Select
Selection.End(xlUp).Select

 

[Domski]

Hi,

Like this I think:

EOMONTH:

With Range("K2:K" & Range("J" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=EOMONTH(RC[-1],RC[2]-1)"
    .Value = .Value
End With

VLOOKUP:

Columns("Y:Y").Insert Shift:=xlToRight
Range("Y1") = "BU"
With Range("Y2:Y" & Range("X" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=VLOOKUP(RC[-1],BU!C[-22]:C[-16],7,0)"
    .Value = .Value
End With

Dom

 

[lakshmipathi123]

Hi,

Like this I think:

EOMONTH:

With Range("K2:K" & Range("J" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=EOMONTH(RC[-1],RC[2]-1)"
    .Value = .Value
End With

VLOOKUP:

Columns("Y:Y").Insert Shift:=xlToRight
Range("Y1") = "BU"
With Range("Y2:Y" & Range("X" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=VLOOKUP(RC[-1],BU!C[-22]:C[-16],7,0)"
    .Value = .Value
End With

Dom

Hi Dom,

It's working...Thanks a lot for your help . I have few more issues that i'll let u know later

Thank you again
Lakshmipathi

 

[lakshmipathi123]

Hi Dom,

It's working...Thanks a lot for your help . I have few more issues that i'll let u know later

Thank you again
Lakshmipathi

Hi,

My Look-up value range contains many blank cells I'd like to remain blank. This code works to populate that range as needed, but the blank cells all show a #NA result. How can I edit this (or another?) macro to leave the blank cells empty?