Adding Another Condition - Use ElseIf?

Question

I am trying to add a line of code in VBA that will ask if the range has a value of HP-1 to then to make it 16 GA. else, it needs to be 26 GA. Currently code just converts everything to 26 GA.

  Sheets("TRIM").Select
    Range("D13").Select
    Do Until ActiveCell.Offset(0, -1).Value = ""
       If ActiveCell.Value <> "" Then
            ActiveCell.Value = "26 GA."
        End If
        ActiveCell.Offset(1, 0).Select
    Loop EndIf

So I need to add in something that will do the HP-1 condition...I did try to make an Else statement but getting NO compile errors but getting this one.

this error has nothing to do with 32bit/64 bit as we are all still using 32bit Excel with 32bit VBA codeI'm sure it is pretty easy but can't seem to get the syntax down... Any suggestions?

This is the original function in total...

Not sure what this is: `Loop EndIf` - Delete `EndIf` from that line, and then see if it compiles. If it doesn't then include more of the code. — braX, Feb 02 '23 at 21:28
Then read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Feb 02 '23 at 21:30

score 0 · Answer 1 · answered Feb 02 '23 at 21:40

0

Nest your If Statements:
Note: depending on your data layout, you may need to find a different way of defining RG, however, this should work.

Option Explicit
Sub Example()
    
    Dim WS As Worksheet
    Dim RG As Range
    Dim CL As Range
    
    Set WS = Worksheets("TRIM")
    
    ' Build Range "RG" from D13 to last used cell in D column
    With WS
        Set RG = .Range("D13", .Range("D" & Rows.Count).End(xlUp))
    End With
    
    ' For each cell in the range "RG"
    For Each CL In RG.Cells
        If CL.Value <> "" Then  'Check if the value of the cell is ""
            If CL.Value = "HP-1" Then   'Check if the value of the cell is "HP-1"
                CL.Value = "16 GA"      'Change Value of the cell
            Else
                CL.Value = "26 GA"      'Change Value of the cell
            End If
        End If
    Next CL
    
End Sub

answered Feb 02 '23 at 21:40

Cameron Critchlow

1,814
1
4
14

This is executed within a function...so should I call this subroutine with in this function? – Brian Feb 02 '23 at 21:53
Is this a function you use as a worksheet formula? – Cameron Critchlow Feb 02 '23 at 22:07
1

@Brian Please read: https://corporatefinanceinstitute.com/resources/excel/vba-sub-vs-function/ A function is supposed to be a calculation or transformation of information/values and return a value, a sub is basically what you're going for now in your function. – Notus_Panda Feb 02 '23 at 22:23

VBasic2008 · Answer 2 · 2023-02-03T00:06:40.857

0

Populate Cells Conditionally

Replace your code with the following.

Dim ws As Worksheet: Set ws = Sheets("TRIM")

If ws.Visible = xlSheetVisible Then
    
    Dim cell As Range: Set cell = ws.Cells(ws.Rows.Count, "C").End(xlUp)
    
    Dim rg As Range, rCount As Long
    
    With ws.Range("D13")
        rCount = cell.Row - .Row + 1
        If rCount > 0 Then Set rg = .Resize(rCount)
    End With
    
    If Not rg Is Nothing Then ' data found
        For Each cell In rg.Cells
            If StrComp(CStr(cell.Value), "HP-1", vbTextCompare) = 0 Then
                cell.Value = "16 GA."
            Else ' is not 'HP-1'
                cell.Value = "26 GA."
            End If
        Next cell
    'Else ' no data found; do nothing!?
    End If
'Else ' worksheet not visible; do nothing!?
End If

edited Feb 03 '23 at 00:06

answered Feb 02 '23 at 23:19

VBasic2008

44,888
5
17
28

Can I ask: from where this different approach of getting the range from `ws.Range("D13")` to lRow? To have one less variable (lRow)? – Notus_Panda Feb 02 '23 at 23:25
`Set cell = ws.Cells(ws.Rows.Count, "C").End(xlUp)` is the 'normal' (popular) approach to get the last cell in a column. Are you asking why I didn't do `Set rg = ws.Range("D13", ws.Cells(ws.Rows.Count, "C").End(xlUp))`? You have probably noticed that OP is looking for the last row in a different column, in column `C`. I consider this pretty risky, so I added the check if the last row in `C` is greater than or equal to `13`. I would say that I have the same number of variables: instead of `lRow` I'm using `rCount` but you are right, I maybe should have used a different variable for the loop. – VBasic2008 Feb 02 '23 at 23:40
I was in no way trying to criticize, just been used to seeing people use `lRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row` and work from there. I'm trying to learn best practices and was wondering if this was one of them :) – Notus_Panda Feb 03 '23 at 07:56

Adding Another Condition - Use ElseIf?

2 Answers2

Populate Cells Conditionally