PDA

View Full Version : [SOLVED] ASSISTANCE NEEDED PLEASE. If..Then.. ElseIf statement that loops.



dboyd616
07-27-2015, 02:49 PM
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
07-27-2015, 08:33 PM
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