How to apply on every row?

Question

I have this code, but i want to apply it to all 4 to 750 rows. I actually do not know much about vba. can somebody help me out?

Sub test()


    
        dinstance = Range("AO4").Value
        Weight = Range("AN4").Value
        Dim type_truck As String
    
        If distance <= 30 And Weight <= 1200 Then
            type_truck = "Large Van"
        ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
            type_truck = "Large Truck 10 - 20t"
        ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
            type_truck = "Large Truck > 20t"
        Else
            type_truck = "LHV"
        End If
    
        Range("AP4").Value = type_truck
   
End Sub

https://stackoverflow.com/questions/24377197/iterating-through-populated-rows — braX, Feb 23 '21 at 14:01

JohnnieL · Answer 1 · 2021-02-23T14:09:40.803

Rather than using a sub to get and set values from ranges, I'd suggest just write your own function - it can take distance and weight as parameters and return a value.

Put a call to the function in AP4 and all your other cells you want a result - https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement

e.g contents AP4 is =test(AO4, AN4)

function test(byval distance as long, weight as long) as string


    
        Dim type_truck As String
    
        If distance <= 30 And Weight <= 1200 Then
            type_truck = "Large Van"
        ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
            type_truck = "Large Truck 10 - 20t"
        ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
            type_truck = "Large Truck > 20t"
        Else
            type_truck = "LHV"
        End If
    
        test = type_truck
   
End function

you may also find it helpful to read up on select case statement https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement

score 0 · Answer 2 · edited Feb 23 '21 at 14:57

Below is some code that will work for you. It begins at row 4 and will go to row 100 (update the 100 if you need to adjust).

-AcingExcel.com

Sub test()


    For i = 4 To 100
        
        distance = Sheet1.Cells(i, "AO")
        Weight = Sheet1.Cells(i, "AN")
        
        dinstance = Range("AO4").Value
        Weight = Range("AN4").Value
        Dim type_truck As String
    
        If distance <= 30 And Weight <= 1200 Then
            type_truck = "Large Van"
        ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
            type_truck = "Large Truck 10 - 20t"
        ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
            type_truck = "Large Truck > 20t"
        Else
            type_truck = "LHV"
        End If
    
        Range("AP" & i).Value = type_truck
    
    Next i
   
End Sub

score 0 · Answer 3 · answered Feb 23 '21 at 17:09

With O365 using Dynamic Arrays and IFS, this should work.

AP4 = IFS(AND(AO4:AO750 <= 30, AN4:AN750 <= 1200), "Large Van",
          AND(AO4:AO750 > 30, AN4:AN750 > 1200, AN4:AN750 <= 7500), "Large Truck 10 - 20t",
          AND(AO4:AO750 > 30, AN4:AN750 > 7500, AN4:AN750 <= 13000), "Large Truck > 20t",
          TRUE, "LHV")

The results will spill into rows 5 to 750.

sous2817 · Answer 4 · 2021-02-23T14:34:03.313

-1

If you're interested in a non VBA approach you can use this formula and drag it down:

=IF(AND($AO4<30,$AN4<=1200),"Large Van",IF(AND($AO4>30,$AN4>1200,$AN4<=7500),"Large Truck 10 - 20t",IF(AND($AO4>30,$AN4>7500,$AN4<=13000),"Large Truck > 20t","LHV")))

Or if you want a VBA solution you could do:

Sub test()
Sheet1.Range("AP4:AP750").Formula = "=IF(AND($AO4<30,$AN4<=1200),""Large Van"",IF(AND($AO4>30,$AN4>1200,$AN4<=7500),""Large Truck 10 - 20t"",IF(AND($AO4>30,$AN4>7500,$AN4<=13000),""Large Truck > 20t"",""LHV"")))"
End Sub

This assumes you want the formula on sheet1 in cells AP4 to AP750.

edited Feb 23 '21 at 14:34

answered Feb 23 '21 at 14:15

sous2817

3,915
2
33
34

Can you give me an example of where it doesn't work? seemed to work fine in my test data and as far as I can tell it's the same rules as the function. – sous2817 Feb 23 '21 at 14:24
wait maybe we fixed it. Give me second – Maurice24 Feb 23 '21 at 14:26
no worries. it's literally the same rules you have in your function, just using Excel's built in functions. So I'd be confused if my formula gave different results than what you or JohnnieL get. – sous2817 Feb 23 '21 at 14:28
the notation of the formula is wrong. it does not detect the rows – Maurice24 Feb 23 '21 at 14:32
are you putting the formula in AP4 and dragging it down? It will automatically update the rows based on how far you drag down the formula – sous2817 Feb 23 '21 at 14:33
holu sh*t it works! but i had to write it all over – Maurice24 Feb 23 '21 at 14:39
well the main thing is you got it to work. Not sure what the issue was originally, but glad to hear you got it sorted. One thing may be regional differences. You may be using a different delimiter for the formula than a ','? Who knows...just speculating. Since the formula (and the VBA) technically work,. hopefully it'll clear that pesky downvote. – sous2817 Feb 23 '21 at 14:40

How to apply on every row?

4 Answers4