Nested InStr VBA Excel

Question

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 ;/

Super Symmetry · Accepted Answer · 2021-01-22T12:31:24.353

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

Nested InStr VBA Excel

1 Answers1