# If Statement Help

#### Final Frontier

##### New Member
I'm trying to create an if statement in VBA for a large spreadsheet I have. I created a sample of the first few lines in Word to help.
 A B C D E F G H I J 1 item number account 1 account 2 amt price jon 10% value 2 83 101 202 2 100 adam 60% 3 84 101 202 3 150 mike 30% 4 85 101 202 4 200

<tbody>
</tbody>

I'm trying to write something that can say if there is a variable in cell E2 down, multiply E2 down by the percentage in column H1. Put the value in M2 down. Repeat for each value in column H. So in this case I would need this process repeated 3 times, first multiplying H1*E2, then E3, E4. Then do the same thing again for H2*E2,E3,E4 etc.

Next I want to copy each value in A2 down in column I2 down for the number of values in column J. So in this case I would need Cells A2-A4 copied and pasted 3 times consecutively.

Thanks!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### bbott

##### Well-known Member
Try this:

Code:
``````Sub MultByH()

Dim y As Long
Dim z As Long

For y = 1 To Range("H" & Rows.Count).End(xlUp).Row
For z = 2 To Range("E" & Rows.Count).End(xlUp).Row
Range("M" & Range("M" & Rows.Count).End(xlUp).Row + 1) _
= Range("E" & z).Value * Range("H" & y).Value
Range("J" & Range("J" & Rows.Count).End(xlUp).Row + 1) = Range("A" & z)
Next z
Next y

End Sub``````

Last edited:

#### Final Frontier

##### New Member
Awesome, thanks a lot! I have a follow up question, I'm trying to use different tabs now instead of having everything on one tab. How can I change the x and y variables to collect info from a specific sheet? Let's just call them Sheet1 and Sheet2. I played around and keep getting 'compile error'. Thanks!

#### bbott

##### Well-known Member
Awesome, thanks a lot! I have a follow up question, I'm trying to use different tabs now instead of having everything on one tab. How can I change the x and y variables to collect info from a specific sheet? Let's just call them Sheet1 and Sheet2. I played around and keep getting 'compile error'. Thanks!

You're welcome.

You would need to add the Sheets object to reference different sheets. You can reference the sheet name or sheet index position. Examples for both would be:

Code:
``For y = 1 to [COLOR=#ff0000][B]Sheets("Sheet1").[/B][/COLOR]Range("H" Rows.Count).End(xlUp).Row``

Code:
``For y = 1 to [COLOR=#ff0000][B]Sheets(1).[/B][/COLOR]Range("H" Rows.Count).End(xlUp).Row``

#### Final Frontier

##### New Member

I tried adding the sheets in that way and it did not work. I must have something off in the code I have. Here's what I have.

Code:
``````Sub HELP1()

Dim y As Long
Dim z As Long

For y = 2 To Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row
For z = 4 To Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row
Sheets("Sheet3").Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1) _
= Sheets("Sheet1").Range("M" & z).Value * Sheets("Sheet2").Range("D" & y).Value
Sheets("Sheet3").Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1) = Sheets("Sheet1").Range("I4" & z)
Next z
Next y

End Sub``````

It might be tough to read without the spreadsheet, but basically I'm trying to multiply variables in Sheet1 and Sheet2 and have them appear in column c in sheet3. Then I want this to loop until it hits all of the values. When I run this I don't get an error message but nothing happens, so I can't figure out where the problem is. Any idea?

#### bbott

##### Well-known Member
You missed two of the Ranges. Also, the statement Range("I4" & z) doesn't make sense. I changed it to Range("I" & z), but I'm not sure if that's what you wanted. Try it like this:
Code:
``````Sub HELP1()

Dim y As Long
Dim z As Long

For y = 2 To Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row
For z = 4 To Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row
Sheets("Sheet3").Range("C" & [B][COLOR=#ff0000]Sheets("Sheet3").[/COLOR][/B]Range("C" & Rows.Count).End(xlUp).Row + 1) _
= Sheets("Sheet1").Range("M" & z).Value * Sheets("Sheet2").Range("D" & y).Value
Sheets("Sheet3").Range("C" & [B][COLOR=#ff0000]Sheets("Sheet3").[/COLOR][/B]Range("C" & Rows.Count).End(xlUp).Row + 1) = Sheets("Sheet1")[B][COLOR=#ff0000].Range("I" & z)[/COLOR][/B]
Next z
Next y

End Sub``````

#### Final Frontier

##### New Member

I'm getting a subscript out of range error message now. Is it possible that I need to designate a variable for I? Like for this portion: For z = 4 To Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row

Would I need to make another variable like For x = 4 To Sheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Row

For each calculation in the code, I need to bring other columns over with it. So would I have to define a variable for each column i want to bring into Sheet3?

Also, this is definitely a beginner question, but how is this code able to paste the data in another worksheet without having paste anywhere in the code? Or does the action of the = sign place the data in the designated cell?

Thanks.

#### bbott

##### Well-known Member
I'm getting a subscript out of range error message now.

I don't get the 'Subscript out of range' error when I run it. Are your sheets named exactly Sheet1, Sheet2, and Sheet3? No other characters or spaces?

Is it possible that I need to designate a variable for I? Like for this portion: For z = 4 To Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp).Row

Variables don't need to be unique to the range. You can re-use and re-set them throughout the routine.

how is this code able to paste the data in another worksheet without having paste anywhere in the code? Or does the action of the = sign place the data in the designated cell?

Yes. Ranges and cells can be set equal to each other without the use of copy and paste.

#### Final Frontier

##### New Member
I got it to work, thanks.

#### bbott

##### Well-known Member

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,627
Messages
5,838,440
Members
430,548
Latest member
hh_dh2001

### 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.

### Which adblocker are you using?

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

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