VBA String Search Loop - Return Matching List

Question

Here is my data:

There are blocks of data on the same sheet, column F will always have the "Controller Firmware Version" at some point and the version will always be one cell below it, as well as D containing the charger serial number two to the left of it.

My desired outcome is a list box compiling all the PK### with their respective firmware versions:

PK### LP2.28
PK### LP#.##
...

Sub Check_Firmware()
    Dim S1$, Firmware As Range, x As Range, ws As Worksheet

    ws = Worksheet(Sheet1)
    Search = "Controller Firmware Version"

    With ws

    Set Firmware = Range("F:F" & Cells(Rows.Count, "F").End(xlUp).Row)
    For Each x In Firmware
        If x.Value2 = "Search" Then
            S1 = S1 & " " & worksheet.function(offset(x.Address(0, 0),1,0)
        End If
    Next

      'How to offset and copy the LP2.28 and compile the results?

End Sub

In your code x.Value2 = "Search" the "Search" is not the variable Search so you should remove the quotes " " here to use the variable Search = "Controller Firmware Version" — Pᴇʜ
– Pᴇʜ, Commented Sep 13, 2018 at 13:07
x.Value2 = "Search" You are looking for the string "Search", not for the string contained in the variable Search. Use x.Value2 = Search. A better way could be to use .Find. You can use x.Offset(1,0).Value2 and x.Offset(1,-2).Value2 to access your data. — Vincent G
– Vincent G, Commented Sep 13, 2018 at 13:08

Vityata · Accepted Answer · 2018-09-13 13:17:54Z

4

Use Option Explicit. It is really a must and it helps you fix about 1 error per line of code.

Option Explicit Reference

This is an example:

Option Explicit

Sub TestMe()

    Dim S1 As String, search As String, Firmware As Range
    Dim x As Range, ws As Worksheet

    Set ws = Worksheets("Sheet1")
    search = "Controller Firmware Version"

    With ws
        Set Firmware = .Range("F1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
        For Each x In Firmware
            If x.Value2 = search Then
                S1 = S1 & " " & x.Offset(1, -2)
                S1 = S1 & " " & x.Offset(1, 0)
                S1 = S1 & vbCrLf
            End If
        Next
    End With

    Debug.Print S1

End Sub

Changes:

declared search variable;
Worksheets instead of Worksheet;
. is added before .Range("F1:F"..., thus the With ws is actually useful;
Range("F:F") would return the whole column. To take a specific number of cell, .Range("F1:F & numberOfCells) is needed. .Range("F:F5") would be an error;
x.Offset() is the function, needed to get the relative value of a range object;
when a worksheet object is assigned, it is done with the word set - Set ws = Worksheets("Sheet1")

edited Sep 13, 2018 at 13:17

answered Sep 13, 2018 at 13:07

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

3 Comments

user4039065 Over a year ago

might want to offset -2 columns.

Vincent G Over a year ago

and offset one row too

AKdelBosque Over a year ago

@vitaya thank you for showing me the option explicit

marc_s · Accepted Answer · 2021-10-31 20:06:16Z

Can be done like this

Option Explicit

Sub Check_Firmware()
    Dim ArrPK() As String, SearchString As String 'Declare ArrPk as string array 
    Dim Firmware As Range, aCell As Range
    Dim ws As Worksheet
    Dim PkCounter As Long
    Dim LstBox As msforms.ListBox

    Set ws = ThisWorkbook.Sheets("Sheet1")        
    SearchString = "Controller Firmware Version"
    Set LstBox = UserForm1.ListBox1
    
    PkCounter = 1
    
    With ws
         'set range that will be source for searching 
        Set Firmware = .Range("F1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)                          
       
        For Each aCell In Firmware 'loop each cell of desired range 
            If aCell.Value2 = SearchString Then 'if match found 
                ReDim Preserve ArrPK(1 To 2, 1 To PkCounter) 'redimension array.
                ArrPK(1, PkCounter) = aCell.Offset(1, 0) 'firmware
                ArrPK(2, PkCounter) = aCell.Offset(1, -2) 'serial no
                PkCounter = PkCounter + 1 'increase counter for next match found 
            End If
        Next
    End With
    
    With LstBox
        .Clear
        .ColumnCount = 2
        .Width = 105
        .ColumnWidths = "50;50"
        For PkCounter = LBound(ArrPK(), 2) To UBound(ArrPK(), 2)
            .AddItem 'add new item to listbox 
              'put values to newly added row 
            .List(PkCounter - 1, 0) = ArrPK(1, PkCounter) 'new row/column 0 
            'PkCounter - 1 because listbox is counted from 0 
            .List(PkCounter - 1, 1) = ArrPK(2, PkCounter)'new row/column 1 
        Next PkCounter
    End With
    
    UserForm1.Show

End Sub

EDIT:

ReDim Preserve ArrPK(1 To 2, 1 To PkCounter)

This sets new dimensions for array
So now you have 2 dimension array.

Preserve means that all values that are already in array will stay there

1 To 2 and 1 to PkCounter are new dimensions for array. When you find more matches then PkCounter grows and so do array.

Put a Breakpoint on With LstBox open "Locals" window. You'll see your ArrPK array there, and you'll be able to check what's inside of it.

You can read more about arrays on the web.

Exactly what I wanted, beautiful - thank you. Can you add a few comments about the ArrPK function and specifically the ArrPK(1 To 2 ...) what the "1 To 2" is referencing.

Collectives™ on Stack Overflow

VBA String Search Loop - Return Matching List

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related