Excel VBA Submit all records with Criteria

Question

I have am trying to complete a function that will submit all records to my excel spreadsheet from a separate spreadsheet, if it matches two criteria's:

• Previously Added Not = "Yes"
• Proposed Action = "Pass"

Currently I have written the code so that it will populate the next record from a request spreadsheet that has not previously been added to the tracker (This workbook), however, every time I try to get it to loop based on the criteria it doesn't work.

Private Sub CmdB_SubmitAll_Click()

Dim wRequest As Workbook
Dim xComponentDetails as Worksheet
Dim ReqLRow As Long
Dim ReqRow As Long
Dim Count As Long
Dim CountDown As Integer
Dim CountUp As Integer

Set wRequest = Workbooks.Open(FilePath)
Set xComponentDetails = wRequest.Worksheets("Component Data")

ReqLRow = xComponentDetails.Cells(Cells.Rows.Count, "B").End(xlUp).Row

Me.CB_Client.Value = ""

With xComponentDetails

        For Count = (ReqRow + 1) To ReqLRow + 1 Step 1
            If Not .Range("W" & Count).Value = "Yes" and Me.TB_PropAction = "Pass" Then
                Call GetBulkRequestData
                            
                ReqRow = Count
                CountUp = WorksheetFunction.CountIf(xComponentDetails.Range("W2:W" & Count), "")
                CountDown = WorksheetFunction.CountIf(xComponentDetails.Range("W" & Count & ":W" & ReqLRow + 1), "")
                Exit For
            End If
        Next Count
        
    If CountUp = 1 Then
        Me.CmdB_Previous.Enabled = False
        Else: Me.CmdB_Previous.Enabled = True
    End If
    
    If CountDown = 1 Then
        MsgBox "No Further Component Requests"
        Me.CmdB_Next.Enabled = False
        Else: Me.CmdB_Next.Enabled = True
    End If
End With

wRequest.Save

End Sub

I'm not a professional coder therefore, any assistance would be gratefully received.

question from:https://stackoverflow.com/questions/66059375/excel-vba-submit-all-records-with-criteria

Answer 1

Waitting for answers