[Help] VBA - Search column, compare, call if match etc.

[WarrenKD]

Hello,

I was hoping to get some help here regarding some VBA code. I've searched through Google so many times and tried multiple ways, but this has totally thrown me off. I'm half decent with VBA, by that I mean I can read it and join sentences to make it work but don't fully understand it... Y'know? I didn't think this would stump me as much as it has, and it seems so simple!

I am looking to use a button on worksheet"D" that then triggers a search on worksheet"B". It will search column D and if column D is lower than or equal to the cell in column E on the same row, then take the info from column A, B and D and place into table "AutoStockCheck" on worksheet"D", then call my email function. Then move onto the next row until it finds a value below again and loop the same options. except it adds into the table, below the previous one. Although when pressing the button on worksheet"D" again, it will clear contents of table to start again.

I've had a go multiple times and deleted the code out of frustration so can't really say where I'm up to

Hope I haven't complicated it within my explanation. Any help is much appreciated and can clarify anything if needed. TIA.

Example of table to search:

 

[HaHoBe]

Hi WarrenD,

maybe this code gets you started (you already have the procedure to mail which call should be added):

Public Sub MrE_1225320_1616610()
' https://www.mrexcel.com/board/threads/help-vba-search-column-compare-call-if-match-etc.1225320/
' Created: 20221224

Dim objLO         As ListObject
Dim lngCnt        As Long
Dim lngWrite      As Long
Dim arrData

Set objLO = Worksheets("D").ListObjects("AutoStockCheck")
With objLO.DataBodyRange
  If .Rows.Count > 1 Then
    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
  End If
End With
objLO.DataBodyRange.Rows(1).ClearContents

With Worksheets("B")
  arrData = .Range("A2", .Cells(.Rows.Count, 5).End(xlUp))
End With

For lngCnt = 1 To UBound(arrData)
  If (arrData(lngCnt, 4) <= arrData(lngCnt, 5)) Then
    lngWrite = lngWrite + 1
    objLO.DataBodyRange(lngWrite, 1) = arrData(lngCnt, 1)
    objLO.DataBodyRange(lngWrite, 2) = arrData(lngCnt, 2)
    objLO.DataBodyRange(lngWrite, 3) = arrData(lngCnt, 4)
    objLO.ListRows.Add AlwaysInsert:=True
  End If
Next lngCnt

Set objLO = Nothing

End Sub

Ciao,
Holger