CODEWITHSUNDEEP
vbaexcelexcel-formula
Matt
Matt

Reputation: 503

Formatting a Formula in a String VBA

I have a script that needs to place a formula into a cell but I'm getting a 1004 error from the first part I am sure I formatted something wrong. I had difficulty with the " marks in the string but got those worked out so I'm figuring I'm missing something else. The cells are also unprotected.

Worksheets(CurSheet + 1).Range("D" & Y).Value = "=IF(D52=1,0,IF(C52=" & """Saturday""" & ",0,'" & CurSheet & "!C" & Y & "))"

This is the section that gives the error. If it is removed code works.

"=IF(D52=1,0,IF(C52="

I am not sure what I am doing wrong with this part.

Upvotes: 0

Views: 153

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

It looks like you're using CurSheet as a sheet index number and as a sheet name.
The index number just returns the relative position of the sheet in the workbook while the name is what you see on the sheet tab (there's also the CodeName but I won't go into that here).

Although I don't fully understand what you're after this code will place a formula on the sheet identified with the sheet index number, so if CurSheet= 1(+1) it will place the formula on the second sheet.
The formula itself will reference the name of the sheet that is before the sheet that the formula appears on (so if the formula is on the second sheet, the formula will reference the first sheet).

Sub Test()

    Dim Y As Long
    Dim CurSheet As Long

    Y = 1
    CurSheet = 1

    Worksheets(CurSheet + 1).Range("D" & Y).Formula = _
        "=IF(D52=1,0,IF(C52=" & """Saturday""" & ",0,'" & Worksheets(CurSheet).Name & "'!C" & Y & "))"

End Sub

Hope I made that clear enough. :)

Upvotes: 1

itChi
itChi

Reputation: 672

You need to declare that you are inputting a formula, not a value:

Change:

Worksheets(CurSheet + 1).Range("D" & Y).Value

To:

Worksheets(CurSheet + 1).Range("D" & Y).Formula

Upvotes: 0

Related Questions