Assistance Needed Please. If..Then.. ElseIf statement that loops.

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. I would prefer to have declared variables, but any solution is appreciated!

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
Alexander BarryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
This should do this for you

Sub test()
    Dim lastrow As Long, i As Long, startrow As Long
    
    startrow = 1
    lastrow = Range("A" & Row.Count).End(xlUp).Row
    
    For i = startrow To lastrow
        If Range("A" & i).Value = "TextA" Then
            Range("D" & i).Value = "TextA"
        ElseIf Range("A" & i).Value = "Text B" Then
            Range("D" & i).Value = "Text C"
        ElseIf Range("A" & i).Value = "" Then
            Range("A" & i).Value = "Null"
            Range("A" & i).Font.Bold = True
            Range("A" & i).Font.Color = vbRed
        End If
    Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FowlerSolutions ConsultantCommented:
Note: My code assumes that the last row is last entry in column A.
0
Elena QuinnCommented:
Try this:
Sub ChooseText()

Dim intNumRows As Double

Dim strTest1 As String
Dim strTest2 As String

strTest1 = "Apple"
strTest2 = "Banana"

'First select all the rows

Columns("A:A").Select
Range(Selection, Selection.End(xlUp)).Select

intNumRows = Selection.Rows.Count

'working one column at a time = first column

For i = 1 To intNumRows


Range(Cells(i, 1), Cells(i, 1)).Select

If Selection.Value2 = strTest1 Then
    Range(Cells(i, 4), Cells(i, 4)).Value2 = strTest1
End If
If Selection.Value2 = "" Then
    Selection.Value2 = "NULL"
    With Selection.Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
    End With
End If
Next

'second column
For i = 1 To intNumRows

Range(Cells(i, 2), Cells(i, 2)).Select

If Selection.Value2 = strTest2 Then
    Range(Cells(i, 4), Cells(i, 4)).Value2 = strTest2
End If
If Selection.Value2 = "" Then
    Selection.Value2 = "NULL"
    With Selection.Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
    End With
End If

Next

End Sub

Open in new window

0
Ryan ChongCommented:
based on your current logic, it can also be simplified by just using pure Excel functions with Conditional Formatting.

see the sample attached for more info.

you can customize the formula:
=IF( ISERROR(FIND("TextA",A2)), IF( ISERROR(FIND("Text B",A2)),  IF( A2= "", "NULL", A2), "Text C"), "Text A")

Open in new window

28700674.xlsx
0
Alexander BarryAuthor Commented:
Thanks for the Support!


The first solution didn't completely work, but I used it as a template to find the final solution. Just a few minor formatting issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.