Search values in columns with VBA


 Hello all,

I have a excel file containing 3 workheets: ID_List, SAP, BOM (excel file in attachement)

In sheet ID_List I have to check for each ID_Number (starting with *XM in line 1):

  1. If the codes below the ID_Number (starting with line 23) are present in sheet SAP> Column C, by checking only lines that match ID_Number.  <YES>/<NO> should be returned in ID_List > Column SAP
  2. If the codes below the ID_Number (starting with line 23) are present in sheet BOM> Column C. <YES>/<NO> should be returned in ID_List > Column SAP

For both checks, if the code format/length is included in a much longer code, the result must be <YES> (E.g. code <A18> in <A180101A02>

Hundreds of columns with IDs are in a single file and introducing formulas in columns (SAP+BOM) is very much time consuming. Could be possible to have a VBA macro to return in automatic those values?

 Many thanks for your support,



Looks like you forgot to attach your file.
John_Ru (rep: 782) Nov 16, '20 at 11:20 am
If you meant to attach a file, you can edit your question and upload it.
don (rep: 1959) Nov 16, '20 at 11:20 am
Sorry, it seems that initial file was larger than allowed. Thanks
sebas14 (rep: 8) Nov 16, '20 at 12:57 pm
Add to Discussion


Selected Answer

Please paste this code to a standard code module (that is one that you have to insert yourself - the default name will be like "Module1" but I recommend to give it a meaningful name: in my workbook it's "TXL_4828" which helps me find this thread again). Then save the workbook in XLSM format.

Option Explicit

' change the output language here:-
Const Outputs   As String = "No,Yes"

Enum Nws                        ' worksheet navigation "ID_List"
    ' 117
    NwsCapsRow = 1                  ' change to suit
    NwsFirstDataRow = 23            ' change to suit
    NwsFirstID = 2                  ' Columns: 2 = column B
    NwsCode                         ' - do - (no set value = previous + 1)
End Enum

Enum Nwl                            ' worksheet navigation lists: "SAP" and "BOM"
    ' 117
    NwlFirstDataRow = 3             ' change to suit
    NwlId = 2                       ' identical in both sheets
    NwlCode                         ' - do - (no set value = previous + 1)
End Enum

Private Enum Ncs                    ' column set
    ' 117
    NcsId                           ' first set column
End Enum

Sub CheckAgainstLists()
    ' 117

    Dim Ws          As Worksheet
    Dim SapId       As Range        ' ID column on SAP tab
    Dim SapCode     As Range        ' Code column on SAP tab
    Dim BomCode     As Range        ' Code column on BOM tab
    Dim Result()    As String
    Dim GrpId       As String       ' Group ID (from column caption)
    Dim Id          As String
    Dim C           As Long         ' loop counter: columns
    Dim R           As Long         ' loop counter: rows

    Set Ws = Worksheets("ID_List")
    With Worksheets("SAP")
        R = .Cells(.Rows.Count, NwlId).End(xlUp).Row
        Set SapId = .Range(.Cells(NwlFirstDataRow, NwlId), .Cells(R, NwlId))
        Set SapCode = .Range(.Cells(NwlFirstDataRow, NwlCode), .Cells(R, NwlCode))
    End With
    With Worksheets("BOM")
        Set BomCode = .Range(.Cells(NwlFirstDataRow, NwlCode), _
                             .Cells(.Rows.Count, NwlCode).End(xlUp))
    End With
    Result = Split(Outputs, ",")

    Application.ScreenUpdating = False
    With Ws
        For C = NwsFirstID To .Cells(NwsCapsRow, .Columns.Count) _
                                   .End(xlToLeft).Column Step NscSetCount
            GrpId = Trim(.Cells(NwsCapsRow, C).Value)
            For R = NwsFirstDataRow To .Cells(.Rows.Count, C).End(xlUp).Row
                Id = Trim(.Cells(R, C).Value)
                .Cells(R, C + NcsSap).Value = Result(Sgn(WorksheetFunction.CountIfs(SapId, GrpId, SapCode, Id)))
                .Cells(R, C + NcsBom).Value = Result(Sgn(WorksheetFunction.CountIf(BomCode, Id & "*")))
            Next R
        Next C
    End With
    Application.ScreenUpdating = True
End Sub

You can change the output language in the first Constant. Change rows and columns in the enums Nws and Nwl. Consider not touching anything else.

The code doesn't produce the result you expect. In the SAP column this may be due to the sample being smaller than your actual database. In the BOM column I attribute it to your result not finding the wild cards. Note that I interpreted your requirement "included" as "starting with". The code will not find "A18" in "xxxA18yyy". Use the worksheet functions to conduct manual searches to remove doubt about what the code does.

Last, not least, the code runs for about 5 seconds on the reduced sample. It will need more time if your db is bigger.



It works great! I made some tests and the result is exactly what I expected. Execution time is also very well, despite big amount of data. Don't even know how to be grateful, you saved me precious time. Many thanks!
sebas14 (rep: 8) Nov 17, '20 at 3:10 am
You gave me a much better question. So you got a much better answer. I'm glad our joint effort paid off so handsomely. That's a pleasure.
Variatus (rep: 4392) Nov 17, '20 at 3:59 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login