excelvbainstr

Nested InStr VBA Excel


I'm trying to loop and find some components in a long list of code to past to different cell.

For now I got this

Sub przenoszenie()
    Dim wb As Workbook
    Dim komorka As Range
    Dim komorka2 As Range
    Set wb = ThisWorkbook
    Set komorka = wb.Worksheets("Dane").Range("B2")
    Set komorka2 = wb.Worksheets("Dane").Range("J2")
    
    For i = 1 To 10000
        If InStr(1, komorka.Value, "width=109") And InStr(1, komorka.Value, "colSpan=8") Then
            komorka2.Value = komorka.Value
        Else
            If InStr(1, komorka.Value, "width=90") Then
                komorka2.Value = komorka.Value
            Else
                If InStr(1, komorka.Value, "width=374") Then
                    komorka2.Value = komorka.Value
                End If
            End If
        End If
        komorka = komorka.Offset(1, 0)
        komorka2 = komorka2.Offset(1, 0)
    Next i

I know that the parts of strings I'm looking for ARE somewhere in that B column of my Excel.
But I'm getting no results in column J. I know I'm missing something in those If statements. Just can't figure it out ;/


Solution

  • Possible Solution:

    Try changing the Instr conditions to Instr(...) > 0 like this:

    Sub przenoszenie()
        Dim wb As Workbook
        Dim komorka As Range
        Dim komorka2 As Range
        Set wb = ThisWorkbook
        Set komorka = wb.Worksheets("Dane").Range("B2")
        Set komorka2 = wb.Worksheets("Dane").Range("J2")
        
        For i = 1 To 10000
            If InStr(1, komorka.Value, "width=109") > 0 And InStr(1, komorka.Value, "colSpan=8") > 0 Then
                komorka2.Value = komorka.Value
            Else
                If InStr(1, komorka.Value, "width=90") > 0 Then
                    komorka2.Value = komorka.Value
                Else
                    If InStr(1, komorka.Value, "width=374") > 0 Then
                        komorka2.Value = komorka.Value
                    End If
                End If
            End If
            Set komorka = komorka.Offset(1, 0)
            Set komorka2 = komorka2.Offset(1, 0)
        Next i
    

    If this doesn't work properly, then another possible issue is case matching. To make your search case-insensitive, use for example Instr(1, komorka.Value, "width=374", vbTextCompare)

    Edit: Just read @JvdV's comments (now deleted) and added Set keyword