Hi, I am confused. I have the following code, but I want to have a formula that says
if C9 = "paid", then E9 = 1, if its not paid, it will then a lookup formula (as per the below), how do I merge the if and vlookup? I then want it to do same formula in the next lot of rows, is just the next code I enter?
HELP
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Multiplier")
Set outputSheet = Worksheets("Example")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
'Apply our formula
.Range("E9:E" & OutputLastRow).Formula = _
"=VLOOKUP(D9,'" & sourceSheet.Name & "'!$B$2:$C$47" & SourceLastRow & ",2,0)"
End With
End With
End Sub
if C9 = "paid", then E9 = 1, if its not paid, it will then a lookup formula (as per the below), how do I merge the if and vlookup? I then want it to do same formula in the next lot of rows, is just the next code I enter?
HELP
Sub MakeFormulas()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
'What are the names of our worksheets?
Set sourceSheet = Worksheets("Multiplier")
Set outputSheet = Worksheets("Example")
'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
'Apply our formula
.Range("E9:E" & OutputLastRow).Formula = _
"=VLOOKUP(D9,'" & sourceSheet.Name & "'!$B$2:$C$47" & SourceLastRow & ",2,0)"
End With
End With
End Sub