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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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
Joined
Sep 16, 2014
Messages
17
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
Joined
Feb 5, 2010
Messages
2,350
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
Joined
Sep 16, 2014
Messages
17

ADVERTISEMENT

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
Joined
Feb 5, 2010
Messages
2,350
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
Joined
Sep 16, 2014
Messages
17

ADVERTISEMENT

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
Joined
Feb 5, 2010
Messages
2,350
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top