Adding Another Condition - Use ElseIf?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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