Excel VBA Runtime error 1004: Application defined or object defined error

Question

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

Charles Williams · Accepted Answer · 2018-03-19 10:22:30Z

3

=F10-C10)

is not a valid formula so you get a 1004

answered Mar 19, 2018 at 10:22

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Callum Over a year ago

Ah that's it. I feel like such an idiot for missing that. Thanks

Vityata · Accepted Answer · 2018-03-19 10:28:33Z

2

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.

answered Mar 19, 2018 at 10:28

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

user4039065 · Accepted Answer · 2018-03-19 10:29:22Z

2

Start with putting a period in front of every Range and Cells within a With ... End With.
Brackets come in pairs.

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

answered Mar 19, 2018 at 10:29

user4039065

Comments

Rory · Accepted Answer · 2018-03-19 13:28:31Z

0

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

answered Mar 19, 2018 at 13:28

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Collectives™ on Stack Overflow

Excel VBA Runtime error 1004: Application defined or object defined error

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related