Macro Run-Time error

[Groves22]

Hey all...
I ran and tested this macro on Friday, and everything worked fine. Now this morning when I run it, I get an error. The error messages the comes up is:

Run-time error '1004':
Application-defined or object-defined error

and it shows up on this line:

Sub Formulae_for_daily_summary()

Dim entered_date As Date
entered_date = InputBox("Please enter the date (MM/DD/YYYY):", "What Date?", Date - 0)

Dim eight_digit_date As Long

With Sheets("History restated")

    .Cells(1, 16).Value = entered_date
    eight_digit_date = .Cells(1, 17).Value

Dim entered_state As String
    
    entered_state = InputBox("Please enter the state abbreviation:")
    
    Dim quote_argument As String, sale_argument As String
    
    quote_argument = "'[" & entered_state & " daily results by agent (" & eight_digit_date & ").xls]1'!C1:C1"
    
    Dim i As Long
    
    i = 3
    
    Do While ((.Cells(i + 1, 2) <> "" And .Cells(i + 2, 2) <> "") Or (.Cells(i + 1, 2) = "" And .Cells(i + 2, 2) <> "") Or (.Cells(i + 1, 2) <> "" And .Cells(i + 2, 2) = ""))
        i = i + 1
    Loop
    
    [b].Cells(3, 4).Resize(i - 2, 1).FormulaR1C1 = "=countif(" & quote_argument & ",RC[-3]&""-""&""Auto""&""-""&""Quote"")"[/b]

I have omitted some lines of code, that do no impact this situation, just an FYI...

Thanks for any help!
Josh

 

[xlbo]

what line does it bomb on?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Groves22]

The bold line in the code, aka the last line.

 

[PHV]

quote_argument use an A1 notation and you play with FormulaR1C1 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Groves22]

Hey PHV...
I'm sorry, I really don't understand what that means... I'm just a novice at VB...

 

[xlbo]

There are 2 ways of referencing a range in a formula....R1C1 (relative to cell) and A1 (absolute to cell)

What PHV is saying is that in your quote_argument, you reference C1:C1 which is in A1 notation but in your formula building, you use RC[-3] which is R1C1 notation. you cannot mix the 2 in one formula



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Groves22]

ah... that makes sense. I wonder why it was working before, though.. oh well.... I'll make the change and post if it doesn't work!

Thanks for the help!