Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Looping from one sheet to another with specific criteria

0

I have been doing beginner vba for about 6 months, however looping is one thing that i still am at a loss with. (This is the year for mastering it!)

I am looking to use the agent ID from the summary tab column A and the specific state that listed at the top row on the Summary tab, to find the last time a state showed in a report, the state could be list in any of the columns on the Source sheet. once its located then i need to check the date to see if its less 30days, more than 30 days or not at all, if under 30 days, "yes", over 30 "yes, needs uptrain", can't find at all, "No". and return back a value back on the source sheet for that agent.
so loops for per agent per state looping thru 5 colums to find the info

I know i need an If, elseif and else statement 

I believe I will need either a For Next or For i loop.

I am not sure on how to proceed, any help would be apprecitated

Sub NestedLoop_GenesysCloudReport()

Dim wb As ThisWorkbook
Dim ws As Worksheet
    Set ws = Worksheets("Source")
Dim Summaryws As Worksheet
    Set Summaryws = Worksheets("Summary")
'declaring lastrow variable
Dim lastrow As Long
    lastrow = ws.Range("A" & Rows.Count).End(xlUp).Row
'setting the Agent ID Range
    Set AgentIDRange = ws.Range("A2:A" & lastrow)

For c = 2 To lastrow

Next c
End Sub

Summary sheet 

Raw Data Sheet Example

Answer
Discuss

Discussion

Helo kmowersvba and welcome to the forum.

If you could upload a sample file (only Excell files can be uploaded), it will make it easier to provide a solution. With a sample file we will be able to see the sheet layout and where to find the data - "unique ID and specific state" and "the last date".
WillieD24 (rep: 537) Dec 31, '22 at 6:46 pm
Add to Discussion

Answers

0
Selected Answer

Hi Kmowersvba and welcome to the Forum.

REVISION 2: 04 Jan 2023 (based on attached file and extra detail in discussion).

As a starter for your code, it's often easier to use Cells (rather than Range) since it has two arguments (rows and columns) so your loop counter can be used to loop in either direction.

In attached file, the revised code below is in a standard module. There's a button on the Summary sheet labelled "Update training status" to which the macro is assigned- click it and it will run. It uses nested loop and has comments to explain what's happening.

Additionally it now doesn't need SOURCE to have equal columns per AgentID row and iy vollects and entries in SOURCE for which there are no matching headers in the Summary sheet (in SOURCE the cells in yellow have changed values with D4 as a space -not a blank, unlike E2 which is one). These get reported in a message box at the end:

Sub NestedLoop_GenesysCloudReport()

Dim SumWs As Worksheet, Ws As Worksheet
Dim SumLstRw As Long, LstRw As Long, SumLstCol As Long, LstCol As Long
Dim c As Long, m As Long, n As Long, p As Long, StaCol As Long
Dim MissSta As String

Set Ws = ThisWorkbook.Worksheets("Source")
Set SumWs = ThisWorkbook.Worksheets("Summary")

On Error Resume Next

'determine last rows
LstRw = Ws.Cells(Rows.Count, 2).End(xlUp).Row
SumLstRw = SumWs.Cells(Rows.Count, 1).End(xlUp).Row
'determine last columns
LstCol = Ws.Cells(1, 1).CurrentRegion.Columns.Count ' since there aren't header rows after B
SumLstCol = SumWs.Cells(1, Columns.Count).End(xlToLeft).Column
' overwrite any previous report
SumWs.Range("B2:G" & SumLstRw).Value = ""

' ### find latest report dates
'loop down known agents in Summary
For c = 2 To SumLstRw
    'loop down AgentsID in source
    For m = 2 To LstRw
        ' compare value in summary column A with Source column B
        If SumWs.Cells(c, 1).Value = Ws.Cells(m, 2).Value Then
            'if matches, loop across Source
            For n = 3 To LstCol
                ' reset state column
                StaCol = 0
                'get matching column number in summary
                StaCol = WorksheetFunction.Match(Ws.Cells(m, n).Value, SumWs.Rows(1), 0)
                If StaCol >= 1 Then
                    If Ws.Cells(m, 1).Value > SumWs.Cells(c, StaCol).Value Then
                        'overwrite date if more recent
                        SumWs.Cells(c, StaCol).Value = Ws.Cells(m, 1).Value
                    End If

                    Else
                    ' if blank cell or state header not found, add to missing states
                    If Ws.Cells(m, n).Value <> "" Then MissSta = MissSta & Ws.Cells(m, n).Address(0, 0) & ":" & Ws.Cells(m, n).Value & ";  "
                End If
            Next n
        End If
    Next m
    ' convert dates to status
    For p = 2 To SumLstCol
        Select Case SumWs.Cells(c, p).Value
            Case ""
            SumWs.Cells(c, p).Value = "No"
            Case Is >= Date - 30
            SumWs.Cells(c, p).Value = "Yes"
            Case Is < Date - 30
            SumWs.Cells(c, p).Value = "Yes, needs uptrain"
        End Select
    Next p
Next c
' tell user is any states were in source but not Summary
If MissSta > "" Then
    MsgBox "Updated but didn't find headers for these entries in " & Ws.Name & vbCr & vbCr & MissSta
    Else
    MsgBox "Updated without problems " & Now
End If

End Sub

It will work with more agents and states, provided they're in column A and row 1 of the Summary sheet. For test purposes, you might want to comment out the Select Case (like a fancy If, EsleIf ,Else) section in bold above- you'll then be able to check that it picked up the right dates.

Hope this fixes your problem- if so, please remember to mark this answer as Selected.

Discuss

Discussion

I uploaded the excel file. I am looking to use the agent ID to find a specific state that listed at the top row on the Summary tab, to find the last time a state showed in a report, the state could be list in any of the columns on the Source sheet. once its located then i need to check the date to see if its less 30days, more than 30 days or not at all. and return back a value back on the source sheet for that agent.
so loops for per agent per state looping thru 5 colums to find the info
kmowersvba (rep: 4) Jan 2, '23 at 10:34 pm
Thanks for attaching the file- I will try to look at it later in my day. Please note that the actaul code from that and the additional detail above ought to be in the question text really- that's where other contributors and users will look mainly.
John_Ru (rep: 6102) Jan 3, '23 at 4:04 am
Please see my revised answer.
John_Ru (rep: 6102) Jan 3, '23 at 6:52 pm
Good evening--
I tested the code in the true file and i am getting "run time error 1004 failing on StaCol code

something is not defined. when i look at what StaCol is equal to it shows 0. Its when the Match cell value is blank because theres no data in it it fails, it needs a cavaet that skips that cell if its blank. Some people will have 3 columns of data and the rest will be blank, others have 4 etc. Thank so much i am learning alot from your code.
kmowersvba (rep: 4) Jan 3, '23 at 10:39 pm
Please see the revised answer/ file (and don't forget to mark it as Selected if it works for you).

Look in the section starting:
For n = 3 To LstCol

to see how I handled headers not found (via StaCol), including collecting them in a new String variable.
John_Ru (rep: 6102) Jan 4, '23 at 9:14 am
Add to Discussion


Answer the Question

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