If Statement Help

Final Frontier

New Member
Joined
Sep 16, 2014
Messages
17
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top