VBA - a set of range objects only once in a loop

Question

I write code that matches the date (from a file), puts it in a collection, and then tries to find it in a spreadsheet. As soon as he finds this, he places the next two items in a collection in two cells. When I run this, I get the following error: "Object variable or With block variable not set". I tried to debug my code, and it shows that after the first code loop below the object of range "rthecell" changes to the correct value. After the second iteration of the loop, the value of "rthecell" changes to "Nothing".

Example:

    Set rtheCell = Range("A:A").Find(What:=LineItem1)
    rtheCell.Offset(, 1).Value = LineItem3 
    rtheCell.Offset(, 2).Value = LineItem2
    Set rtheCell = Nothing

Again, everything works as intended at the first iteration of the loop, but I get an error after the second iteration.

Here is the complete code:

Sub InputData()

'Declare variables

Dim sFilePath As String
Dim sLineFromFile As String
Dim saLineItems() As String
Dim element As Variant
Dim col As Collection
Dim LineItem1 As String
Dim LineItem2 As String
Dim LineItem3 As String
Dim rtheCell As Range

Set col = New Collection

'Insert file path name here, this file will be overwritten each morning

sFilePath = "P:\Billing_Count.csv"

Open sFilePath For Input As #1

Do Until EOF(1)

    Line Input #1, sLineFromFile

    'Split each line into a string array
    'First replace all space with comma, then replace all double comma with single comma
    'Replace all commas with space
    'Then perform split with all values separated by one space

    sLineFromFile = Replace(sLineFromFile, Chr(32), ",")
    sLineFromFile = Replace(sLineFromFile, ",,", ",")
    sLineFromFile = Replace(sLineFromFile, ",", " ")
    saLineItems = Split(sLineFromFile, " ")

    'Add line from saLineItem array to a collection
    For Each element In saLineItems
        If element <> " " Then
        col.Add element
        End If
    Next

Loop

Close #1

'Place each value of array into a smaller array of size 3
Dim i As Integer
i = 1

Do Until i > col.Count


    'Place each value of array into a string-type variable

    'This line is the date
    LineItem1 = col.Item(i)
    i = i + 1
    'This line should be the BW count make sure to check
    LineItem2 = col.Item(i)
    i = i + 1
    'This line should be the ECC count make sure to check
    LineItem3 = col.Item(i)
    i = i + 1

    'Find the matching date in existing Daily Billing File (dates on Excel must be formatted as
    'general or text) and add ECC and BW counts on adjacent fields

    Set rtheCell = Range("A3:A37").Find(What:=LineItem1)
    rtheCell.Offset(, 1).Value = LineItem3 'This is LineItem3 since we can ECC data to appear before BW
    rtheCell.Offset(, 2).Value = LineItem2
    Set rtheCell = Nothing
    LineItem1 = 0

Loop

'Format cells to appear as number with no decimals
'Format cells to have horizontal alignment
Sheets(1).Range("B3:C50").NumberFormat = "0"
Sheets(1).Range("C3:C50").HorizontalAlignment = xlRight


End Sub

Answer 1

Range.Find, After:= , Range.FindNext, After:= . - , ( ), .

dim fndrng as range, fndstr as string
set fndrng = Range("A:A").Find(What:=LineItem1, after:=cells(rows.count, "A"))
if not fndrng is nothing then
    fndstr = fndrng.address
    do while True

        'do stuff here

        set fndrng = Range("A:A").FindNext(after:=fndrng)
        if fndstr = fndrng.address then exit do
    loop
end if

, . tbh, .