CODEWITHSUNDEEP
vbaexcelloopsmacros
IIJHFII
IIJHFII

Reputation: 600

VBA Loop not returning any values, plus failure to terminate

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

Upvotes: 0

Views: 137

Answers (2)

Clyde
Clyde

Reputation: 193

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

Upvotes: 3

user4039065
user4039065

Reputation:

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.

Upvotes: 4

Related Questions