Macro To Update Data in Cell Based On Value In Adjacent Cell

[Howardc]

Where corporate signage and Generator appears in Col A, I would like the narrarive in Col E that appears in the same row as these two items to be changed to ADMINISTRATION. When activating the Macro only the narrative In Col E pertaining to Corporate Signage in Col A is changed to ADMINISTRATION

Your assistance in resolving this is most appreciated



Sub ChangeTextInColumnE()
Const sA As String = "Corporate Signage"
Const sB As String = "Generator"

Const sE As String = "Administration"
Const sF As String = "Administration"
Dim lr As Long, r As Range, vA As Variant, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A2", "E" & lr)
vA = r.Value
Application.ScreenUpdating = False
For i = LBound(vA, 1) To UBound(vA, 1)
If UCase(vA(i, 1)) = UCase(sA) Then r.Cells(i, 5) = sE
If UCase(vA(i, 1)) = UCase(sB) Then r.Cells(i, 5) = sF
Next i
End Sub

[Excel Fox]

Please used code tags to wrap codes. I have added it for you this time.

Don't see any apparent issues, except maybe for trimming the values. try this


Sub ChangeTextInColumnE()

Const sA As String = "Corporate Signage"
Const sB As String = "Generator"

Const sE As String = "Administration"
Const sF As String = "Administration"
Dim lr As Long, r As Range, vA As Variant, i As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("A2", "E" & lr)
vA = r.Value
Application.ScreenUpdating = False
For i = LBound(vA, 1) To UBound(vA, 1)
If Trim(UCase(vA(i, 1))) = Trim(UCase(sA)) Then r.Cells(i, 5) = sE
If Trim(UCase(vA(i, 1))) = Trim(UCase(sB)) Then r.Cells(i, 5) = sF
Next i

End Sub

[Howardc]

Thanks for the help. The text was generators and not generator, which caused the problem. should have used a wildcard