VLookup

[waxb18]

Hi having problems with this macro

At the moment it is copying down the formula to cell G5000 but this is a variable sometimes it can be G2000 sometime G10000,

Can someone show me how to edit this so no matter what it pastes down to the last cell of data

Thanks in advance

Sub VlookupRM()
'
' VlookupRM Macro
'
'
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G5000")
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H:H")
Range("H:H").Select
ChDir _
"G:\Stats"
End Sub

 

[VoG]

Try

Sub VlookupRM()
'
' VlookupRM Macro
'
'
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Range("G2:G" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("H2:H" & LR).FormulaR1C1 = "=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"
ChDir "G:\Stats"
End Sub

 

[Maheshp]

Sir VoG

Normally to find last row we can use below code

LR = Range("A" & Rows.Count).End(xlUp).Row

How below Line works ?
what does * mean ?
this is Last row of which column ?
SearchDirection:=xlPrevious means which column ?

LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

i will appreciate if you explain

 

[VoG]

I don't know which column has the last value.

* looks for anything.

xlPrevious looks upwards.

 

[Maheshp]

Thank u so much ! have check results with below two codes found results matching

'code 1
Sub test()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
MsgBox LR
End Sub
'code 2
Sub test1()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
MsgBox LR
End Sub

 

[waxb18]

Both of you are geniuses
I thank you both humbly

One issue i am having now though is getting the file to save and close.
What i now want to do is close the Engineer.xls file and Save as the extract to Call Data (Todays Date).xls

Sub apply_vlookups()

Bdir = "G:\Stats\Engineers.xls"
Workbooks.Open (Bdir)

myworkbook = "Call Data Extract TEMPLATE.xls"

Windows(myworkbook).Activate
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("F2:F" & LR).FormulaR1C1 = "=VLOOKUP(RC[-1],'[All Engineers.xls]Eng List'!C1:C4,2,FALSE)"
Range("G2:G" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("H2:H" & LR).FormulaR1C1 = "=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"

End Sub

 

[VoG]

Try

Sub apply_vlookups()

Bdir = "G:\Stats\Engineers.xls"
Workbooks.Open (Bdir)

myworkbook = "Call Data Extract TEMPLATE.xls"

Windows(myworkbook).Activate
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("F2:F" & LR).FormulaR1C1 = "=VLOOKUP(RC[-1],'[All Engineers.xls]Eng List'!C1:C4,2,FALSE)"
Range("G2:G" & LR).FormulaR1C1 = "=VLOOKUP(RC[-2],'[All Engineers.xls]Eng List'!C1:C4,3,FALSE)"
Range("H2:H" & LR).FormulaR1C1 = "=VLOOKUP(RC[-3],'[All Engineers.xls]Eng List'!C1:C4,4,FALSE)"
ActiveWorkbook.SaveAs Filename:="Call Data " & Format(Date, "mm-dd-yyyy") & ".xls"
Workbooks("Engineers.xls").Close
End Sub

 

[waxb18]

It saved just as i want it to

However when closing i get the following error:

Run-Timer error '9':

Subscript out of range

 

[VoG]

Do you have any other code in the workbook? What happens if you click Debug?

 

[waxb18]

this is auto sub which is included in the workbook

Sub polo()
Open_Extract_and_COPYANDPASTEDATA
VoG
End Sub

When i click Debug i get the same error