Inserting value conditionally based on another column's value with VBA

Question

I am trying to:
Search each value in column S.
If the value = "CA", select the cell in the same row but in column I.
If the value of that cell is <= 93599, select the original cell in column S, and overwrite it with "CS".
If the (column I) cell value is > 93599, select the original cell in column S, and overwrite it with "CN".
Then continue checking the rest of the cells in column S.

Dim wks As Worksheet
Dim LastRow As Long
Dim cell As Range
Dim a As String

Set wks = ActiveWorkbook.Worksheets("Addresses")
   
With wks

    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    If LastRow = 1 Then
        Exit Sub
    End If
            
    a = "93599"
    
    For Each cell In .Range(.Cells(2, 19), .Cells(LastRw, 19))
    
        If cell = "CA" Then
            ActiveCell.Offset(0, -10).Select
             
            If ActiveCell.Value <= a Then
                ActiveCell.Offset(0, 10).Select
                ActiveCell.Value = "CS"
             
            Else
                ActiveCell.Offset(0, 10).Select
                ActiveCell.Value = "CN"
    
            End If
    
        End If

    Next cell

End With

This code returns

"Run-time error '1004': Application-defined or object-defined error"

and highlights the ActiveCell.Offset(0, -10).Select which suggests that a cell in column S is not selected when it's trying to perform the offset.

Are the values you're checking against `a` actually strings, or numbers? — Tim Williams, Jun 22 '21 at 18:35
@TimWilliams so they were originally numbers (the values in column I), but I ran Selection.NumberFormat = "00000" in previous code to convert them to zipcodes. Also, If I change it to Dim a as Integer, I get an overflow. — saeglopur12, Jun 22 '21 at 18:43
`ActiveCell.Offset(0, -10).Select` - you have not activated the cell in Col S before calling this, so if the activecell is left of Col K this will fail as you're trying to select a zero or negative column number — Tim Williams, Jun 22 '21 at 18:43
You've only applied a format to your numbers, so .Value still reads the numeric value. Integer maxes out at around 32000, so you'd need Long in this case. — Tim Williams, Jun 22 '21 at 18:44

score 0 · Accepted Answer · answered Jun 22 '21 at 18:39

If the column I values are numeric then comparing them to a string may not be ideal.

Sub tester()
    Dim cell As Range, a
    
    a = 93599
    With ActiveWorkbook.Worksheets("Addresses")
        For Each cell In .Range("S2:S" & .Cells(.Rows.Count, "S").End(xlUp).Row).Cells
            If cell.Value = "CA" Then
                Select Case cell.EntireRow.Columns("I").Value
                    Case Is <= a: cell.Value = "CS"
                    Case Is > a: cell.Value = "CN"
                End Select
            End If
        Next cell
    End With
End Sub

Note you typically never need to activate/select anything when working with ranges. See:

How to avoid using Select in Excel VBA

score 0 · Answer 2 · answered Jun 22 '21 at 19:15

Just a matter of taste and for presenting an alternative in the way it's formatted, here is my answer

Public Sub AjustValues()

    ' Set target value
    Dim targetValue As Long
    targetValue = 93599

    ' Get reference to addresses sheet in workbook running the code
    Dim AddrSheet As Worksheet
    Set AddrSheet = ThisWorkbook.Worksheets("Addresses")
    
    ' Get last row in column A (see that you can refer to the column letter instead of the number)
    Dim lastRow As Long
    lastRow = AddrSheet.Cells(AddrSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Exit procedure if last row is 1
    If lastRow = 1 Then Exit Sub
    
    ' Loop through each in column S starting from row 2 ending in last row
    Dim sourceCell As Range
    For Each sourceCell In AddrSheet.Range("S2:S" & lastRow)
        
        ' Check cell value
        If sourceCell.Value = "CA" Then
            
            ' Choose according to the value in column I
            Select Case sourceCell.Offset(0, -10).Value
            Case Is <= targetValue
                sourceCell.Value = "CS"
                
            Case Is > targetValue
                sourceCell.Value = "CN"
            End Select
        End If
        
    Next sourceCell
    

End Sub

Inserting value conditionally based on another column's value with VBA

2 Answers2