VBA Loop not returning any values, plus failure to terminate

Question

I have written a bit of code that is intended to search cells in a column, see if they start with a certain string and then return a value based on that string in another column. I have two problems, firstly the loops don't actually return any values in columns 8,9,10 or 11. Also the second loop doesn't stop running? Here is my code

Sub Possible_solution_one()

Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

Set ws = ActiveSheet

For i = 2 To ws.Cells(ws.Rows.Count, "a").End(xlUp).Row
    Do While Cells(i, 1).Value <> ""

        If Cells(i, 2) = "Business://EXTRACTS/" & "*" Then
            Cells(i, 8) = "OBS(" & Cells(i, 2).Value & ",SHARE,#DI) OR "
        End If

        i = i + 1
    Loop
Next


For j = 2 To ws.Cells(ws.Rows.Count, "a").End(xlUp).Row
    Do While Cells(j, 6).Value <> ""
        If Cells(j, 6) = "Business" & "*" Then
            Cells(j, 9) = "OBS(" & Cells(j, 4).Value & ",SHARE,DI) OR "

        ElseIf Cells(j, 6) = "CSTM" Then
            Cells(j, 10) = "PUM(" & Cells(j, 4).Value & ",#D7) OR "

        ElseIf Cells(j, 6) = "*FS" Then
            Cells(j, 11) = "FCON(" & Cells(j, 4).Value & ") OR "

        End If
        i = i + 1
    Loop
Next

End Sub

To give the situation I have 1 type of string in column B and 3 types in column F. Am looking to return different things in columns 8,9,10,11 based on b and D

Have changed those around but still am getting the same problems :/ — IIJHFII
– IIJHFII, Commented Feb 24, 2016 at 14:11

user4039065 · Accepted Answer · 2016-02-24 14:14:59Z

4

If you are pattern matching with wildcards, you need to use the Like operator. e.g. If Cells(i, 2) Like "Business://EXTRACTS/" & "*" Then

The Do While loops inside the For Next loops were unnecessary. It is also not a good idea to 'manually' increment the increment counter in a For ... Next loop.

The second loop was running forever because you were incrementing i, not j.

A Select Case statement would make a better fit for the multiple criteria in the second j loop.

Sub Possible_solution_one()
    Dim i As Long, j As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws
        For i = 2 To .Cells(.Rows.Count, "a").End(xlUp).Row
            If Not CBool(Len(Cells(i, "a").Value)) Then Exit For
            If .Cells(i, 2) = "Business://EXTRACTS/" & "*" Then
                .Cells(i, 8) = "OBS(" & .Cells(i, 2).Value & ",SHARE,#DI) OR "
            End If
        Next i

        For j = 2 To .Cells(ws.Rows.Count, "a").End(xlUp).Row
            Select Case .Cells(j, 6).Value
                Case "Business*"
                    .Cells(j, 9) = "OBS(" & Cells(j, 4).Value & ",SHARE,DI) OR "
                Case "CSTM"
                    .Cells(j, 10) = "PUM(" & Cells(j, 4).Value & ",#D7) OR "
                Case "*FS"
                    .Cells(j, 11) = "FCON(" & Cells(j, 4).Value & ") OR "
                End If
            End Select
        Next j
    End With

End Sub

I've also incorporated a With ... End With statement to associate all of the cells to the parent ws worksheet. Note hte .Cells and not Cells. The prefixing period assigns parentage to the worksheet referenced in the With ... End With.

With no sample data, I could not completely test this rewrite but it does compile.

answered Feb 24, 2016 at 14:14

user4039065

Sign up to request clarification or add additional context in comments.

2 Comments

IIJHFII Over a year ago

It works!! thank you so much. Unrelated question, how have you learnt so much about VBA, I am very new to VBA but in the position where I will be using it alot in the future so am looking to do some learning in my spare time? thanks again for the help :)

user4039065 Over a year ago

If you want to examine real world examples, this site is one of the best. Looking through the [excel], [vba] or [excel-vba] forums, possibly sorted with the highest votes up top or searched for topics that are of interest is a great way to pick up tips and hone your skills. The better questions come with sample data that can be copied and pasted into a blank workbook and code that you can step through with [F8].

Clyde · Accepted Answer · 2016-02-24 14:18:02Z

3

your second do while loop uses i=i+1 instead of j = j + 1 so it's not going to increment the cells(j, 6).value if there's anything in cells(j,6) then the loop won't stop running

For j = 2 To ws.Cells(ws.Rows.Count, "a").End(xlUp).Row
Do While Cells(j, 6).Value <> ""
    If Cells(j, 6) = "Business" & "*" Then
        Cells(j, 9) = "OBS(" & Cells(j, 4).Value & ",SHARE,DI) OR "

    ElseIf Cells(j, 6) = "CSTM" Then
        Cells(j, 10) = "PUM(" & Cells(j, 4).Value & ",#D7) OR "

    ElseIf Cells(j, 6) = "*FS" Then
        Cells(j, 11) = "FCON(" & Cells(j, 4).Value & ") OR "

    End If
    'i = i + 1
    j = j + 1
Loop
Next

answered Feb 24, 2016 at 14:18

Clyde

1937 bronze badges

2 Comments

user4039065 Over a year ago

Good catch but I didn't find that the nested Do While .. Loop inside the For ... Next to be necessary at all.

Clyde Over a year ago

totally agree - your answer was much more comprehensive

Collectives™ on Stack Overflow

VBA Loop not returning any values, plus failure to terminate

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related