asked on

VBA Excel Vlooup Macro Help for Dynamic Range

Respected All,

Hope you are all Fine i have recorded a macro with vlookup formula it comes in R1C1 format Please advice how to make Criteria Range Which is (Sheet1!R[-57]C[-5]:R[186]C[17] = B1:S244) Dynamic so that if some data Added in last row which will be 245 it Will auto include in range.

Sub VlookupExtractRecoveries()

'Code to Apply Vloockup Formula to Extract Figures in Recovery Report
Dim LR As Long
LR = Range("G58").End(xlDown).Row

Range("G58").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],Sheet1!R[-57]C[-5]:R[186]C[17],COLUMNS(Sheet1!R[152]C[-5]:R[152]C[10]),0),0)"
Range("G58").Select
Selection.AutoFill Destination:=Range("G58:G" & LR), Type:=xlFillDefault
Range("G58").Select

'Code to Convert Formula as Peste Special value

Range(Selection, Selection.End(xlDown).Offset(-1)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Code for Esc Copy Cut or Cancel Sellectoion
Application.CutCopyMode = False
End Sub

Farhan Mahmood

ASKER

=VLOOKUP(B58,Sheet1!B1:X244,COLUMNS(Sheet1!B210:Q210),0)

Shums Faruk

Try below:

Sub VlookupExtractRecoveries()
Dim Ws As Worksheet
'Code to Apply Vloockup Formula to Extract Figures in Recovery Report
Dim LR As Long
LR = Ws.Range("B" & Rows.Count).End(xlDown).Row

Ws.Range("G58:G" & LR).FormulaR1C1 = "=IFERROR(VLOOKUP(RC2,Sheet1!R1C2:R244C24,COLUMNS(Sheet1!R210C2:R210C17),0),0)"
  'Code to Convert Formula as Peste Special value
  
Ws.Range("G58:G" & LR).Value = Ws.Range("G58:G" & LR).Value
End Sub

Open in new window

Farhan Mahmood

ASKER

Respected Sir,
I Want This Range Sheet1!R1C2:R244C24 dynamic instead G58 to G
Please Kindly Review My Question.
Thanks Aloot For Earlier Response.
Waiting???
Regards

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Farhan Mahmood

ASKER

Dear Sir Shums,

Your Code Helped me with a Little bit addition of Offset. Thanks

Sub VlookupExtractRecoveries9()
Dim Ws As Worksheet
'Code to Apply Vloockup Formula to Extract Figures in Recovery Report
Dim LR As Long
Set Ws = ActiveSheet
LR = Ws.Range("G57").End(xlDown).Row

Ws.Range("G57:G" & LR).Offset(-1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC2,Sheet1!C2:C24,COLUMNS(Sheet1!R210C2:R210C17),0),0)"
'Code to Convert Formula as Peste Special value

Ws.Range("G57:G" & LR).Offset(-1).Value = Ws.Range("G57:G" & LR).Offset(-1).Value
End Sub