While working on a problem with my counter in my loop
, The_Barman posted a link to Patrick's article on using the Dictionary Class In VBA
I have read the article and follow some of it and I get the concept, but working into my loop is a bit over my head. TheBarman was kind enough to let me know I could post a question for some direction (Okay, a lot of help!) Patrick's article gave two excellent examples, which I have stepped through several times, just not sure how to work into my current code.
Below is my current code. The project is pretty straight forward. I import an Excel file which contains a list of vacation request (Approved and Rejected) into my "Imported" worksheet (it's a temp worksheet which is cleared before each import). I keep a list of "Approved" and "Rejected" as a yearly summary in their respectively named worksheets. Each record (request) has a unique identifier in the A column. Since I import from Jan 1st each time, I have duplicate request from prior imports. My current loops separates the "approved" and "rejected" into the appropriate worksheet, but it doesn't check for any previously imported records with the same unique ID#.
I hope this makes sense.
Thanks for the help and I'll study the code in detail.
Dim ApprovedSheet As Worksheet
Dim ImportedSheet As Worksheet
Dim RejectedSheet As Worksheet
Dim iCol As Long, iRow As Long, iRowStart As Long, iRowEnd As Long, iNextRow As Long
Dim UniqueIDApprovedWorksheetRng As Range
Dim UniqueIDRejetectedWorksheetRng As Range
Set ApprovedSheet = ThisWorkbook.Worksheets("Approved")
Set ImportedSheet = ThisWorkbook.Worksheets("Imported")
Set RejectedSheet = ThisWorkbook.Worksheets("Rejected")
'//////// My ApprovedWorksheet has an extra column, so this add's the extra column prior to copying
'//////// This will be for my counter
iRowStart = 2
iRowEnd = Cells(Rows.Count, "A").End(xlUp).Row ' Find the last row of data
iCol = 8
For iRow = iRowStart To iRowEnd
If ImportedSheet.Cells(iRow, iCol).Value = "Approved" Then
'/// move to appropriate sheet
iNextRow = ApprovedSheet.Cells(ApprovedSheet.Rows.Count, 1).End(xlUp).Row + 1
ApprovedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value
iNextRow = RejectedSheet.Cells(RejectedSheet.Rows.Count, 1).End(xlUp).Row + 1
RejectedSheet.Cells(iNextRow, 1).Resize(1, iCol).Value = ImportedSheet.Cells(iRow, 1).Resize(1, iCol).Value