VBA Macro. I Think I'm Close But Something Is Not Right

[zunebuggy]

I have an Excel sheet that has date in this format:



Group ID
Complete Path
Filename






0
C:\Folder1\R0CVNM6.pdf
R0CVNM6.pdf






0
J:\FolderA\f303919232.pdf
f303919232.pdf






1
C:\Folder1\R0YC44P.pdf
R0YC44P.pdf






1
J:\FolderA\c34563.pdf
c34563.pdf






1
H:\FolderZ\t342332.pdf
t342332.pdf






1
K:\FolderS\j73838a.pdf
j73838a.pdf







etc...

The Group ID goes up to 51374 but there are 115590 rows in this spreadsheet with the Group ID having a minimum of 2 identical numbers (i.e. two 0's, four 1's, etc.). The maximum count having the same Group ID is 21.

I would like my script to go through each row 0 through 115590 and take the 1st row of each Group ID and move that file to a folder and all the rest with the same Group ID, move it to a different folder. Then move on to the next Group ID and do the same. Repeat...

Here is my first attempt, but it only copied 1 file and then skipped the rest.


Sub MoveFiles()
zz = 2
For j = 0 To 20 '51374
aa = Range("A" & zz)
bb = Range("B" & zz)
cc = Range("C" & zz)
If j = aa And fth = 0 Then
fth = 1
Name bb As "J:\kfolder\" & cc
End If
If j = aa And fth = 1 Then
Name bb As "J:\ffolder\" & cc
End If
zz = zz + 1
Next j
End Sub

There has to be a better approach to this.

Also, some of these filenames (very few) have special characters and Excel errors on these. I would like Excel to just ignore and maybe flag or highlight these.

I do know how to use Kill to delete the final files, but I want to get the code working first before deleting a bunch of files.

Thank you,
Zune

[Aussiebear]

Welcome to VBAX Zunebuggy. What does the variable fth stand for?

[zunebuggy]

Welcome to VBAX Zunebuggy. What does the variable fth stand for?

In my mind, "First Time Here". It's just a flag.

[zunebuggy]

I was trying to use that so it only moves the first unique Group ID to the kfolder and the rest with that same Group ID to the ffolder.

[zunebuggy]

I see that I need to reset this back to zero every time the Group ID number changes. My Excel sheet is sorted by Group ID if that makes it easier. I did check and there are 2 or more of rows with the same Group ID. Many have 2 of the same Group ID, some have 3, some have 4, some have 5, all the way up to the maximum count is 21. I guess if they each just had two of the same Group IDs it would be easy. I could just For Next it sending Odd numbered rows to one folder and the even numbered rows to another, but the sheet is more complicated than that.

[Paul_Hossler]

I think that you're over thinking it




Option Explicit


Sub MoveFiles()
Dim rFiles As Range
Dim r As Long

Set rFiles = ActiveSheet.Cells(1, 1).CurrentRegion


With rFiles
For r = 2 To .Rows.Count
If .Cells(r, 1).Value = .Cells(r - 1, 1).Value Then ' same group
' Name CStr(.Cells(r, 2).Value) As "J:\kfolder\" & .Cells(r, 3).Value
Debug.Print "Group = " & .Cells(r, 1).Value & " -- Move " & CStr(.Cells(r, 2).Value); " to " & "J:\kfolder\" & .Cells(r, 3).Value
Else
' Name CStr(.Cells(r, 2).Value) As "J:\ffolder\" & .Cells(r, 3).Value ' row r is one group and row r-1 is another
Debug.Print "Group = " & .Cells(r, 1).Value & " -- Move " & CStr(.Cells(r, 2).Value); " to " & "J:\ffolder\" & .Cells(r, 3).Value
End If
Next r
End With
End Sub


Debug output



Group = 0 -- Move C:\Folder1\R0CVNM6.pdf to J:\ffolder\R0CVNM6.pdf
Group = 0 -- Move J:\FolderA\f303919232.pdf to J:\kfolder\f303919232.pdf
Group = 1 -- Move C:\Folder1\R0YC44P.pdf to J:\ffolder\R0YC44P.pdf
Group = 1 -- Move J:\FolderA\c34563.pdf to J:\kfolder\c34563.pdf
Group = 1 -- Move H:\FolderZ\t342332.pdf to J:\kfolder\t342332.pdf
Group = 1 -- Move K:\FolderS\j73838a.pdf to J:\kfolder\j73838a.pdf

[zunebuggy]

Thank you. That worked very well. :yes