CODEWITHSUNDEEP
stringvbaexcelfor-loop
AKdelBosque
AKdelBosque

Reputation: 105

VBA String Search Loop - Return Matching List

Here is my data:

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

Upvotes: 1

Views: 526

Answers (2)

Sphinx
Sphinx

Reputation: 660

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.

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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

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")

Upvotes: 4

Related Questions