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