If Statement

[spicekid]

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

 

[Michael M]

like this
AND
can you please use code tags when posting code in future

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 = "=IF(C9=""paid"",1,VLOOKUP(D9,'" & sourceSheet.Name & "'!$B$2:$C$47" & SourceLastRow & ",2,0))"

End With

End With

End Sub

 

[spicekid]

Thanks you for the reply.

.Range("E9:E" & OutputLastRow).Formula = "=IF(C9=""paid"",1,VLOOKUP(D9,'" & sourceSheet.Name & "'!$B$2:$C$47" & SourceLastRow & ",2,0))"

The problem I have with this is that it puts a 1 in the whole of Column E, I want it to basically look at C9 if it is paid, then it would put 1, however, if C10 says contractor, then it would be then do a vlookup on D10 and so on. There are about 22000 rows.

How do I amend this to look at the next row after it has done the above?

Hope this makes sense

Thanks for all your help
Alan

 

[Michael M]

you didn't tell us that in the first post.
you need to tell us the whole story not just the parts that you THINK are relevant !!

 

[spicekid]

Calm Down...thanks for your advice...it worked with the formula given

 

[Michael M]

I am calm...but creating a line of code is easier with ALL the criteria at the start, rather than adding extra bits as we go along.
I'm more than happy to modify the code further, if required.