2
votes

I've scoured the internet for an answer to my problem. I am writing some code to input a formula into certain cells on a worksheet and despite very similar code working perfectly earlier in the macro, this section of code will not work with giving me the runtime error 1004: application-defined or object-defined error.

I have tried moving my code into a new workbook but the problem was not solved and I just can't see why it won't work. The code below is where I define the sheets I am using

Sub InputFormulae()

Dim wksht As Worksheet
Dim wksht1 As Worksheet
Dim wksht2 As Worksheet
Dim wksht3 As Worksheet
Dim wksht4 As Worksheet
Dim wksht5 As Worksheet

Set wksht = ThisWorkbook.Worksheets("Coils same day remove & insert")
Set wksht1 = ThisWorkbook.Worksheets("Implants same day remove&insert")
Set wksht2 = ThisWorkbook.Worksheets("Implant inserted NO Removal")
Set wksht3 = ThisWorkbook.Worksheets("Implant inserted AND removed")
Set wksht4 = ThisWorkbook.Worksheets("Coil inserted NO removal")
Set wksht5 = ThisWorkbook.Worksheets("Coil inserted AND removed")

The code below is a part of the macro that is working

wksht.Activate
With wksht
   i = Range("A" & Cells.Rows.Count).End(xlUp).Row

    Do Until i = 1
        If .Cells(i, 1) <> "" Then
        Cells(i, 9).Formula = "=IF(A" & i & "=A" & i + 1 & ",IF(C" & i & "=C" & i + 1 & ",(H" & i & "-C" & i & "),(F" & i + 1 & "-C" & i & ")),IF(A" & i & "=A" & i - 1 & ",IF(C" & i & "=C" & i - 1 & ",(H" & i & "-C" & i & "),(H" & i & "-C" & i & ")),(H" & i & "-C" & i & ")))"
        End If
        i = i - 1
    Loop
End With

And the code below here is the part that is not working

wksht3.Activate
With wksht3
   i = Range("A" & Cells.Rows.Count).End(xlUp).Row

   Do Until i = 1
If .Cells(i, 1) <> "" And .Cells(i, 3) <> "" And .Cells(i, 6) <> "" Then
        Cells(i, 9).Formula = "=F" & i & "-C" & i & ")"
        Else: Cells(i, 9).Value = "0"
        End If
        i = i - 1
    Loop
End With

When I debug the code it highlights the Cells(i, 9).Formula = "=F" & i & "-C" & i & ")" line

Thanks for your time

4

4 Answers

3
votes
=F10-C10)

is not a valid formula so you get a 1004

2
votes

The error you get is because VBA does not understand "=F" & i & "-C" & i & ")". As far as it is a string, the easiest way to debug is to write either:

debug.print "=F" & i & "-C" & i & ")" on the line above and to see the immediate window for the value

or

MsgBox "=F" & i & "-C" & i & ")" on the line above and to see the string in a MsgBox.

Based on the result you would know how to continue.

2
votes
  1. Start with putting a period in front of every Range and Cells within a With ... End With.

  2. Brackets come in pairs.

  3. Don't turn real numbers into text-that-looks-like-a-number.

    wksht3.Activate   '<~~ totally unnecessary to use a With ... End With
    With wksht3
        i = .Range("A" & .Cells.Rows.Count).End(xlUp).Row
    
        Do Until i = 1
            If .Cells(i, 1) <> "" And .Cells(i, 3) <> "" And .Cells(i, 6) <> "" Then
               .Cells(i, 9).Formula = "=F" & i & "-C" & i
           Else
               .Cells(i, 9).Value = 0
           End If
           i = i - 1
        Loop
    End With
    
0
votes

FWIW, you could also just have your formula do the tests too:

With wksht3
   i = Range("A" & Cells.Rows.Count).End(xlUp).Row
   .Range("I1:I" & i).FormulaR1C1 = "=IF(OR(RC1="""",RC3="""",RC6=""""),0,RC6-RC3)"
End With