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]

Re: AutoFill within a Userform VBA

I tried both suggestions but with no luck.

 

[Fluff]

Re: AutoFill within a Userform VBA

What is findcode?

 

[lwarren30]

Re: AutoFill within a Userform VBA

It's a lookup function for a value to add information to that respective row. Here's the rest of the code. Everything else works.

'Newely added account code
'Looks for GL code above new line
Set AddedAcct = Sheet1.Range(Cells(lst_GL_AR_RowSelection.ListIndex + 6, 7), Cells(lst_GL_AR_RowSelection.ListIndex + 6, 7))


    'Income
    Sheet3.Select
    
        'Skips sheets that dont have the code
        On Error Resume Next
    
        'Finds newely added row in other sheet
        Set FindCode = ActiveSheet.Range("A:A").Find(what:=AddedAcct, LookIn:=xlValues, lookat:=xlWhole)
    
        'Insert rows
        ActiveSheet.Rows(FindCode.Row & ":" & FindCode.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
        'Insert formula for GL code and name
        ActiveSheet.Select
        FindCode.Offset(-1, 0) = "=General_Ledger!G" & lst_GL_AR_RowSelection.ListIndex + 5
        FindCode.Offset(-1, 3) = "=General_Ledger!R" & lst_GL_AR_RowSelection.ListIndex + 5
        
        'Turns on errors
        On Error GoTo 0
        
        '****** Unable to use the autofil function*******
        '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))

 

[Fluff]

Re: AutoFill within a Userform VBA

If you remove this line

 On Error Resume Next

what happens?

 

[lwarren30]

Re: AutoFill within a Userform VBA

It makes no difference. The code is running throw multiple sheets and if it doesn't find the code the On Error skips the error message and goes to the next sheet. I'm trying all three suggestions with no luck:

        'Experiment 1
        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))
        
        'Experiment 2
        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
        
        'Experiment 3
        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))

 

[Fluff]

Re: AutoFill within a Userform VBA

Add this msgbox as show

Set findcode = ActiveSheet.Range("A:A").Find(what:=AddedAcct, lookIn:=xlValues, lookat:=xlWhole)
MsgBox findcode.Address(False, False) & vbLf & findcode.Parent.name & vbLf & findcode.Parent.CodeName
'Insert rows
ActiveSheet.Rows(findcode.Row & ":" & findcode.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

What does the message box say?

 

[lwarren30]

Re: AutoFill within a Userform VBA

It says:
"
A13
Income
Sheet3
"

 

[Fluff]

Re: AutoFill within a Userform VBA

Try

With Sheet3
    .range(.Cells(findcode.Row - 2, 8), .Cells(findcode.Row - 2, 26)).AutoFill .Range(.Cells(findcode.Row - 2, 8), .Cells(findcode.Row, 26))
End With

 

[lwarren30]

Re: AutoFill within a Userform VBA

That worked!!! Can you explain what you did? How did that msgbox help you?

 

[Fluff]

Re: AutoFill within a Userform VBA

The message box just confirmed that the findcode range was ok & was on the correct sheet.
If the range had been A2, rather than A13 then you would not have been able to go up 2rows.

When using Autofill the destination must include the range you are filling from.