G
Guest
I'm frustrated already, and it's not even 8:30!
I've got a bunch of tracking forms with two sheets - one called TOTALS and
one called ACCOUNTS. On the ACCOUNTS sheet, columns A-H are used for people
to list accounts they own, and put their names next to it. I wrote a sub
that, among other things, checks to see if the names from the ACCOUNTS tab
appear on the TOTALS tab. As part of it, I use the .Find method to check
this.
I had it working at one point, but the tracking forms changed and I had to
fiddle with my cod a bit. The ONLY change to the forms that would impact this
code is the range where the names would be is in a different place. However,
I know that I'm capturing the right range. Here is my code, with comments as
to what is going on. RG is the range of names from the TOTALS sheet that I
want to check against the ACCOUNTS sheet.
Private Sub CheckNames(RG As Excel.Range)
Dim objProduction As Excel.Range, objLastRow As Excel.Range
Dim I As Byte, lngMax As Long
Dim objC As Excel.Range, objF As Excel.Range
Set objProduction = objCurrent.Worksheets("ACCOUNTS").Range("A4:H600")
' I PUT THIS IN TO MAKE SURE THAT THE NAMES FROM THE TOTALS SHEET ARE
BEING PICKED UP - THEY ARE
For Each objC In RG.Cells
MsgBox objC.Value
Next
For Each objC In objProduction.Cells
With objC
' Column returns a number based on the column of a cell
Select Case .Column
Case 2, 4, 6, 8 ' Columns B,D,F,H - the columns with names
If Trim(.Offset(0, -1)) <> "" Then ' There is an account
number to the left of the current column
If Trim(.Value) = "" Then
Call ErrorWrite("ACCOUNT " & .Offset(0, -1) & " NOT
ASSIGNED TO AN RSA.", strCurrentFile, strMonth, strBranch)
Else
Set objF = RG.Find(.Value) ' <<-- THIS ALWAYS FAILS,
EVEN THOUGH .Value IS A NAME THAT FALLS WITHIN RANGE "RG", WHICH I VERIFIED
WITH THE MsgBox ABOVE
If objF Is Nothing Then ' Name was not found
Call ErrorWrite("THE NAME " & UCase(.Value) & "
LISTED ON ACCOUNTS TAB BUT NOT ON TOTALS TAB.", strCurrentFile, strMonth,
strBranch)
End If
End If
End If
End Select
End With
Next
End Sub
I've got a bunch of tracking forms with two sheets - one called TOTALS and
one called ACCOUNTS. On the ACCOUNTS sheet, columns A-H are used for people
to list accounts they own, and put their names next to it. I wrote a sub
that, among other things, checks to see if the names from the ACCOUNTS tab
appear on the TOTALS tab. As part of it, I use the .Find method to check
this.
I had it working at one point, but the tracking forms changed and I had to
fiddle with my cod a bit. The ONLY change to the forms that would impact this
code is the range where the names would be is in a different place. However,
I know that I'm capturing the right range. Here is my code, with comments as
to what is going on. RG is the range of names from the TOTALS sheet that I
want to check against the ACCOUNTS sheet.
Private Sub CheckNames(RG As Excel.Range)
Dim objProduction As Excel.Range, objLastRow As Excel.Range
Dim I As Byte, lngMax As Long
Dim objC As Excel.Range, objF As Excel.Range
Set objProduction = objCurrent.Worksheets("ACCOUNTS").Range("A4:H600")
' I PUT THIS IN TO MAKE SURE THAT THE NAMES FROM THE TOTALS SHEET ARE
BEING PICKED UP - THEY ARE
For Each objC In RG.Cells
MsgBox objC.Value
Next
For Each objC In objProduction.Cells
With objC
' Column returns a number based on the column of a cell
Select Case .Column
Case 2, 4, 6, 8 ' Columns B,D,F,H - the columns with names
If Trim(.Offset(0, -1)) <> "" Then ' There is an account
number to the left of the current column
If Trim(.Value) = "" Then
Call ErrorWrite("ACCOUNT " & .Offset(0, -1) & " NOT
ASSIGNED TO AN RSA.", strCurrentFile, strMonth, strBranch)
Else
Set objF = RG.Find(.Value) ' <<-- THIS ALWAYS FAILS,
EVEN THOUGH .Value IS A NAME THAT FALLS WITHIN RANGE "RG", WHICH I VERIFIED
WITH THE MsgBox ABOVE
If objF Is Nothing Then ' Name was not found
Call ErrorWrite("THE NAME " & UCase(.Value) & "
LISTED ON ACCOUNTS TAB BUT NOT ON TOTALS TAB.", strCurrentFile, strMonth,
strBranch)
End If
End If
End If
End Select
End With
Next
End Sub