Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'This is an error on my part. Should be = False
'If we turn ScreenUpdating off, the code will run faster, as it doesn't have to spend time
'updating the screen, and user doesn't see a lot of images flying across their screen
Application.ScreenUpdating = True
'Where is the source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")
'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
Set outputSheet = ThisWorkbook.Worksheets("Sheet1")
'Determine last row of source
'With Statements allow a type of shorthand. Note that the next line has a "." before Cells. This
'means that the Cells traces back to the With object. We use With when we want to not have to write as much
'This also lets the code run a little faster, as it doesn't have to do quite as much thinking
'to find out what we are working with.
With sourceSheet
'The latter half of equation says to go to the last row in col A, and then "go up" until
'it runs into data. This lets us find the last row of information we want to
'be concerned with
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'This is equivalent to writing this line:
'SourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
End With
'Another With Statement
With outputSheet
'Similar to before, want to find the last row of used information
'in col P of the outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "P").End(xlUp).Row
'Apply our formula
'We want to put a formula in all of the cells. Rather than doing this one cell at a time,
'it is much more efficient to put the formula in the entire range. Since we
'know what the OutputLastRow is now, we will apply the formula to some dynamic range in
'column Q.
'The VLOOKUP function needs to know the name of our workbook and our sheet of interest,
'so we call that information. The ampersand "&" lets us concatenate VB variables with
'text strings, so that we can construct the overall formula
.Range("Q2:Q" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With
'Close the source workbook, don't save any changes
'The False at the end of the line is Optional. If we didn't have it, the Code would stop
'and ask us if we want to save. We prevent this by purposely telling the code we don't
'want to save
sourceBook.Close False
'Turn the screen updating back on. Again, we should have turned this off at beginning
Application.ScreenUpdating = True
End Sub
hope my reply is not too long.