Simple =IF Statement for ActiveSheet.Range.Value in Excel VBS triggers 1004 Runtime Error

Question

Got the following code which DOES WORK as an Excel VBScript Macro:

Sub replace()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Sheets(ws.Name).Activate

        Dim I
        For I = 5 To 20
            ActiveSheet.Range("T" & I).Value = "=AVERAGE(3)"
        Next I
    Next ws
End Sub

Obviously =AVERAGE(3) is not meant to be useful in any way, but to show that this code above works, and the following chunk does not:

Sub replace()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Sheets(ws.Name).Activate

        Dim I
        For I = 5 To 20
            ActiveSheet.Range("T" & I).Value = "=IF(1=1;1;0)"
        Next I
     Next ws
End Sub

Actually there should be a way more complex formula where the IF is (which looks something like: =IF(Q5<>0;IF(R5+S5>Q5;"ERROR";IF(R5="";S5/Q5;IF(Q5=R5;"Coord.";S5/(Q5-R5))));"N/A").

The thing is, I narrowed it down until I saw that even that trivial =IF(1=1;1;0) statement fails with the 1004 runtime error.

I don't think it's of importance to solve the issue, but the whole macro is just for the purpose of updating about 15 cells on each worksheet for about 31 worksheets. As I said, the first chunk works (with the AVERAGE() calculation) but the =IF does not work.

For the sake of completeness, this was the code that I wanted to have:

Sub updCells()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    Sheets(ws.Name).Activate

        Dim I
        For I = 5 To 20
        ActiveSheet.Range("T" & I).Value = "=IF(Q" & I & "<>0,IF(R" & I & "+S" & I & ">Q" & I & ",""ERROR"",IF(R" & I & "="""",S" & I & "/Q" & I & ",IF(Q" & I & "=R" & I & ",""Coord."",S" & I & "/(Q" & I & "-R" & I & ")))),""N/A"")"

        Next I

     Next ws
End Sub

Try replacing the semi-colons with commas. (And using the Formula property rather than Value.) Oh, and you don't need the second loop. — SJR, Jan 09 '19 at 10:43
Some Scandinavian language require the semi-colon in place of the comma. On my comma-requiring PC assigning the "=IF(1=1,1,0)" works fine even when assigning to Value but using semi-colons fails with error 1004. That surprised me because I was expecting Excel to be able to recognize a Value as Text. Apparently the leading = sign prevents that. Presuming that Kondado knows that his PC needs semi-colons, it would appear that his version of Excel reacts differently from mine when a formula is (wrongly) assigned to Value. — Variatus, Jan 09 '19 at 11:25
BTW, I wouldn't call a procedure "Replace" because "Replace" is both a VBA and Excel Object and using the name can cause misunderstandings (= crashes). Before using a name, select it and press F1. — Variatus, Jan 09 '19 at 11:27
@Variatus It's not that Kondado's Excel reacts differently. It's that although some setups use semi-colon as separator in formulas written in worksheet, VBA is the same for everyone and you must use commas. — Egan Wolf, Jan 09 '19 at 12:38
@Egan Wolf Interesting thought but I stumble over the fact that the code assigns a string to the cells's Value property. Can't help feeling that Excel is stumbling over its own feet here. — Variatus, Jan 10 '19 at 01:13

score 0 · Accepted Answer · answered Jan 09 '19 at 10:50

0

Your code could be reduced to the following as in addition to comments you don't need to activate sheets. See how to avoid select.

This will also generally work for more complex formulae (such as your example) - Excel will work out the relative references.

Sub replace()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Range("T5:T20").Formula = "=IF(1=1,1,0)"
Next ws

End Sub

answered Jan 09 '19 at 10:50

SJR

22,986
6
18
26

Thanks SJR and all others who have answered!!! The reason was using semi-colon instead of comma. I usually have to use semi-colon when typing the formula directly into Excel, but for some reason VBScript expects a simple comma. I also re-checked the system settings, as I had also been experimenting with converting between CSV and Excel for some data analysis, but even if I changed the setting and restarted Excel it still expected a comma in VBScript. I had also checked Excels own settings in this aspect. Anyway, you saved my day!! Thanks again! – Kondado Jan 10 '19 at 12:44
My pleasure. As an addendum you can use `FormulaLocal` instead and retain your semi-colons. – SJR Jan 10 '19 at 14:46

Simple =IF Statement for ActiveSheet.Range.Value in Excel VBS triggers 1004 Runtime Error

1 Answers1