If Statement

spicekid

New Member
Joined
Mar 22, 2016
Messages
12
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
like this
AND
can you please use code tags when posting code in future
Code:
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

New Member
Joined
Mar 22, 2016
Messages
12
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 !!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,763
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I am calm.:biggrin:..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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top