Error code when copying down formual in excel vba

I'm trying to copy down a formula in excel using vba but I keep getting this error message for all my cells except the first cell:

"Error #1004 - Application-defined or object-defined error".

Why would I get that?

Sub AddFontFormula()
    Range("I2").Formula = "=strFont_Color(C2)"
    Range("I2").AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, 3).End(xlUp).Row)
End Sub

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
What does this mean?
I keep getting this error message for all my cells except the first cell
Your code runs perfectly with me. If it were not to run it could through only one error on Line 3. It couldn't error out on every cell.
Therefore I presume that you have the UDF 'strFont_Color' in your workbook which is throwing the error, perhaps because there are no values in column C or the wrong values.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The formula that gets put into cell I2 works but when the filldown of the formula happens the rest of the rows show that error. But when I manually filldown the formula it works fine. So only when the filldown vba code fires I get the error. How could the UDF cause that? Here's the UDF code:

Public Function strFont_Color(ByRef rngCell As Range) As String

  Dim strReturn                                         As String
  
  On Error GoTo Err_strFont_Color
  
  Select Case (rngCell.Font.Color)
  
      Case (vbBlack)
          strReturn = "Black"
  
      Case (vbBlue)
          strReturn = "Blue"
  
      Case (vbRed)
          strReturn = "Red"
          
      Case (vbYellow)
          strReturn = "Yellow"
          
      Case Else
          strReturn = CStr(rngCell.Font.Color)
          
  End Select
  
Exit_strFont_Color:

  On Error Resume Next
  
  strFont_Color = strReturn
  
  Exit Function
  
Err_strFont_Color:

  strReturn = "Error #" & CStr(Err.Number) & " - " & Err.Description
  
  Resume Exit_strFont_Color
  
End Function

Open in new window

0
SteveCommented:
How about not using autofill:

Sub AddFontFormula()
    Range("I2:I" & Cells(Rows.Count, 3).End(xlUp).Row).Formula = "=strFont_Color(C2)"
End Sub

Open in new window

0
FaustulusCommented:
This code worked with me:-
Sub AddFontFormula()
    Application.Calculation = xlCalculationManual
    Range("I2").Formula = "=strFont_Color(C2)"
    Range("I2").AutoFill Destination:=Range("I2:I" & Cells(Rows.Count, 3).End(xlUp).Row)
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.