ASSISTANCE NEEDED PLEASE. If..Then.. ElseIf statement that loops.

[dboyd616]

I’m looking to figure out an automation process for an excel file I have, using VBA code. I’m fairly new to VBA coding, and my last few attempts have been unsuccessful. I was hoping I could possibly find some guidance on the subject?

There are multiple sheets within the excel file, but all work will be done via the ‘Activesheet’ for ease of use. There are over 800 rows that this code would have to run through, so it needs to be some sort of loop.

Please help if possible, and let me know if I need to provide any more information for a proper diagnosis. Thanks!


Example of structure (in my mind):

If Column A contains “TextA” Then _
Column D will contain “TextA”

ElseIf Column A contains “Text B” Then _
Column D will contain “Text C”

ElseIf Column A contains Nothing Then_
Column A will now contain “Null” in Bold Red font

Else

End If
Next Loop (next row)
End Sub

[NoSparks]

Assuming your data starts on row 2, think you're looking for something along the lines of this

Sub dboyd616()
    Dim rng As Range    'the range to work with
    Dim cel As Range    'individual ranges within rng
    Dim lastrow As Long
    
'to stop screen flicker and speed things along
Application.ScreenUpdating = False

With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & lastrow)
    
    'this For Each...Next is your loop
    For Each cel In rng
        If cel.Value = "TextA" Then
            cel.Offset(0, 3).Value = "TextA"
        ElseIf cel.Value = "TextB" Then
            cel.Offset(0, 3).Value = "TextC"
        ElseIf cel.Value = "" Then
            With cel
                .Value = "Null"
                .Font.Bold = True
                .Font.Color = vbRed
            End With
        End If
    Next cel
    
End With
    
'turn screenupdating back on
Application.ScreenUpdating = True

End Sub