Problem with the Offset Property of a Range Object

[pcdaugs]

Hello Everyone,

I am currently writing some code to produce a client list that tells me who bought a product from what producer(sale rep or agent). The first step in this process is to reformat a excel spread sheet that is extracted from a .PDF file so that it will build a list that shows the producer and the clients they sold. Then I need to do some data validation code to compare this compiled list to a list of clients from another report in excel and paste in the producer that sold this client. The goal is to get all this information into one contiguous format so that I can run pivot tables and charts from it.

So far I have to code at the bottom to search the .PDF extracted excel report the producer and their clients. I was having trouble with the Find property in that I was searching for two different values, producer and client. When I initialized each search I would use the FindNext property to find the next occurrence. The problem was that the FindNext property would search for the next occurrence of which ever search, the producer or client, that was last to be initialized. I solved this problem by using the Find property and its After:= argument to reinitialize the search I wanted and to tell it where to start searching.

However, now I have another problem and I believe it is with the Offset property I utilize with my range object rFndC in a zero-based parsing buffer. The runtime error that is produced when I run my code is "Runtime Error 9: Subscript is out of range" on the bold and italic line in my code.

[b][i]rOut(1, "E").Value = astr2(1)[/i][/b]

After running through my code line by line with F8 I found that the reason for this error is because astr2(1) is calling for the second word from the string of the client's name. The problem is that the string passed to the astr2() in the parsing buffer was "LTC" which is the value of cell B99 not A99 like the offset property should have caused it to grab. The runtime error occurring in the line above is happening because B99 is only one word so looking for the word in the second position of a zero-based parsing buffer is not there and is going to throw and error.

This is why I believe the offset property of my zero-based parsing is the culprit and I can't figure out why this line mis functions. This line of code works fine the first eight times through it and then miss fires the ninth time.

[b][i]astr2 = Split(WorksheetFunction.Trim(rFndC.Offset(0, -1).Value), " ")[/i][/b]

I can't figure it out and I would be considered a novice so if anyone can give me a hand I would much appreciate it.

Thanks in Advance,

pcdaugs

Full Code

Sub FindProducerClient()
     
Dim rFndP1   As Excel.Range ' Producer found range of Sheet2
Dim rFndP2   As Excel.Range ' Next Producer found range of Sheet2
Dim rFndC    As Excel.Range ' Client found range of Sheet2
Dim rCount1  As Excel.Range
Dim rCount2  As Excel.Range
Dim sAdrP    As String ' Address of first found Producer Cell
Dim sAdrC    As String ' Address of first found Client Cell
Dim astr1()  As String ' Zero-based parsing buffer for rFndP1
Dim astr2()  As String ' Zero-based parsing buffer for rFndC
Dim iCount   As Integer
 
Dim rOut    As Excel.Range ' Output range on Sheet1
 
'Clear output sheet and write header
Sheet1.Cells.ClearContents
Sheet1.Range("A1").Resize(, 6) = Array("ProducerFN", "ProducerMI", "ProducerLN", _
"ClientFN", "ClientMI", "ClientLN")
 
'Initialize search for the producer
Set rFndP1 = Sheet2.Cells.Find( _
What:="Producer:", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Set rFndP2 = Sheet2.Cells.FindNext(rFndP1)
If rFndP1 Is Nothing Then Exit Sub

'Initialize search for the client
Set rFndC = Sheet2.Cells().Find( _
What:="LTC", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

' save the address of the first found cell
sAdrP = rFndP1.Address

'First Do While Loop searchs for Producers
Do
    
    'rCount1 & rCount2 act as a limit for Nested Do While Loop
    'Set rCount1 = to column 2
    If rFndP1.Column = 1 Then
    Set rCount1 = rFndP1.Offset(0, 1)
    Else
    Set rCount1 = rFndP1
    End If
        
    'Set rCount2 = to column 2
    If rFndP2.Column = 1 Then
    Set rCount2 = rFndP2.Offset(0, 1)
    Else
    Set rCount2 = rFndP2
    End If

    'Reinitializes search for clients and allows the Nested Do While Loop to
    'utilize the FindNext() property for clients.
    Set rFndC = Sheet2.Cells.Find( _
    What:="LTC", After:=rFndC, LookIn:=xlValues, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

        'Nested Do While Loop searchs for the clients associated with the producer
        'that has been found in first Do While Loop. Then it will parse and paste
        'each producer and client in one row.
        Do
            'Zero-based parsing buffers for producer and client
            astr1 = Split(WorksheetFunction.Trim(rFndP1.Value), " ")
            [b]astr2 = Split(WorksheetFunction.Trim(rFndC.[i]Offset(0, -1)[/i].Value), " ")[/b]
            
            Set rOut = Sheet1.Cells(Rows.Count, "A").End(xlUp).Offset(1)
            'Output of Producer's First, MI, and Last name
            rOut(1, "A").Value = astr1(1)
            rOut(1, "B").Value = astr1(2)
            rOut(1, "C").Value = astr1(3)
            
            'Output of Client's First, MI, and Last name
            'The if statement check if the client only has their first and last name listed
            'and outputs the names in the proper cell location.
            If 2 = Len(WorksheetFunction.Trim(rFndC.Offset(0, -1).Value)) - _
            Len(WorksheetFunction.Substitute(rFndC.Offset(0, -1).Value, " ", "")) + 1 Then
            
            rOut(1, "D").Value = astr2(0)
            rOut(1, "F").Value = astr2(1)
            
            Else
            rOut(1, "D").Value = astr2(0)
            [b][i]rOut(1, "E").Value = astr2(1)[/i][/b]
            rOut(1, "F").Value = astr2(2)
            End If

            Set rFndC = Sheet2.Cells.FindNext(rFndC)
            iCount = iCount + 1
        Loop While iCount < WorksheetFunction.CountIf(Range(rCount1, rCount2), "LTC")
    
    'Reinitializes search for producers and allows utilization of the FindNext() property
    'for producers.
    Set rFndP1 = Sheet2.Cells.Find( _
    What:="Producer:", After:=rFndP1, LookIn:=xlValues, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    
    Set rFndP2 = Sheet2.Cells.FindNext(rFndP1)
    'Resets iCount to be used in Nested Do While Loop
    iCount = 0
    
Loop While rFndP1.Address <> sAdrP
 
End Sub

 

[SkipVought]

It's not the Offset property, IMHO.

Use the Watch Window to determine what is happening. faq707-4594

What is the watch window on...

rOut

astr2(1)

rFndC



Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}