Using VBA to insert a dynamic variable formula ERROR

[lwarren30]

Good afternoon,

This is my first time posting a question on this thread but have seen many How To for my Excel projects. I've been using Excel for over 10+ years and VBA about 3 so it's not too often I come across something I cant figure out.

I am trying to insert a variable formula nested within a Do Until Loop. The only variable is LineNumber to move down within a column. I have debugged all the syntax errors but now have a new error: "Run-Time error '1004': Application-defined or object-defined error".

Per the Internet (haha), this error means a variable is not defined yet all are and have values.

Dim LineCount As Integer
Dim LineNumber As Integer

LineNumber = 9


'Due Date & Reccurring & Amount Columns
LineCount = Sheet7.Application.CountA(Range("A9:A500")) - 1 + 8


Do Until LineNumber = LineCount + 1


    Cells(LineNumber, 10).Formula = _
    "=IF(G" & LineNumber & "="""","""",IF(G" & LineNumber & "=""""Monthly"""",F" & LineNumber & "/12,IF(G" _
    & LineNumber & "=""""Deadline"",IF(I" & LineNumber & "="","",F" & LineNumber & "/(MONTH(I" & LineNumber & ")-MONTH(H" _
    & LineNumber & ")+1)),F" & LineNumber & ")))"
    '=IF(G9="","",IF(G9="Monthly",F9/12,IF(G9="Deadline",IF(I9="","",F9/(MONTH(I9)-MONTH(H9)+1)),F9)))


    LineNumber = LineNumber + 1


Loop

The original (functional) formula is below the vba insert code for reference. Please let me know if you have a solution or questions.

Thank you,

Larry

 

[lwarren30]

AutoFill within a Userform VBA

Good afternoon,

I am trying to use a AutoFill to copy formulas to a row below within a userform subroutine button. I tested it in a normal module and it works fine but it will not with the userform.

'Insert formula for month
Cells(Cells(FindCode.Row - 2, 8), Cells(FindCode.Row - 2, 26)).Select
Selection.AutoFill Destination:=Range(Cells(FindCode.Row - 1, 8), Cells(FindCode.Row, 26))

Please let me know if missing something.

Larry

 

[MARK858]

Re: AutoFill within a Userform VBA

Try specifically referencing the sheet (and remove the Select/Selection).

 

[Norie]

Re: AutoFill within a Userform VBA

Don't know if you solved your first post but the error message you describe does not indicate undeclared variables, it indicates a problem with the formula in the code.

The problem with the formula in the code is with the quotes, try this.

    Cells(LineNumber, 10).Formula = _
    "=IF(G" & LineNumber & "="","",IF(G" & LineNumber & "=""Monthly"",F" & LineNumber & "/12,IF(G" _
    & LineNumber & "=""Deadline"",IF(I" & LineNumber & "="","",F" & LineNumber & "/(MONTH(I" & LineNumber & ")-MONTH(H" _
    & LineNumber & ")+1)),F" & LineNumber & ")))"

 

[MARK858]

Re: AutoFill within a Userform VBA

Deleted as being stupid

 

[lwarren30]

Re: AutoFill within a Userform VBA

For some reason this post attached to my first post. Disregard this one and I'll create a new posting for it.

 

[lwarren30]

Re: AutoFill within a Userform VBA

Awesome! That worked. Thank you!

Would you be able to help with my Autofill problem?

 

[MARK858]

Re: AutoFill within a Userform VBA

Would you be able to help with my Autofill problem?

The response I made in post number 3 was to your autofill question and my response still stands

 

[lwarren30]

Re: AutoFill within a Userform VBA

Try specifically referencing the sheet (and remove the Select/Selection).

I try this but it gave me a "Runtime error 1004".

Sheet3.Cells(Cells(FindCode.Row - 2, 8), Cells(FindCode.Row - 2, 26)).AutoFill Destination:=Range(Cells(FindCode.Row - 1, 8), Cells(FindCode.Row, 26))

 

[Fluff]

Re: AutoFill within a Userform VBA

You need to qualify all the ranges/cells like

Sheet3.Cells(Sheet3.Cells(FindCode.row - 2, 8), Sheet3.Cells(FindCode.row - 2, 26)).AutoFill Destination:=Sheet3.Range(Sheet3.Cells(FindCode.row - 1, 8), Sheet3.Cells(FindCode.row, 26))

or

With Sheet3
   .Cells(.Cells(FindCode.row - 2, 8), .Cells(FindCode.row - 2, 26)).AutoFill Destination:=.Range(.Cells(FindCode.row - 1, 8), .Cells(FindCode.row, 26))
End With