VBA Setting Formula in Range - Problems

Question

I have the following problem. I want to fill in the formula (="") in different cells, it is necessary to fill in this kind of formula instead of leaving the cells empty due to processing by another file. However, if I loop over the different cells, in the following way:

For Col = 10 To 99
    For Row = 10 To 11
        If Col Mod 3 <> 0 Then
           Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Formula = "="""
           Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Locked = True
           Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Interior.ThemeColor = xlThemeColorLight2
           Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Interior.TintAndShade = 0.799981688894314
       End If
    Next Row
Next Col

Then I receive an error that the formula can not be assigned. Nevertheless I do not see what does the error produces.

Thanks a lot for your help

Kind Regards Claude

Possible duplicate of [Placing Double Quotes Within a String in VBA](https://stackoverflow.com/questions/42960548/placing-double-quotes-within-a-string-in-vba) — Pᴇʜ, Jul 26 '17 at 07:54

score 2 · Accepted Answer · answered Jul 26 '17 at 07:53

2

It's kinda confusing when you try to put " in cell via VBA. You need to escape it with "...

Try this:

"="""""

answered Jul 26 '17 at 07:53

Egan Wolf

3,533
1
14
29

Thanks a lot, it was really, only that little mistake of not having enough " in the formula. I wanted to write the formula ="" and in transforming it into the code I missed some "" . Thanks a lot – Claude G Jul 26 '17 at 07:57
@ClaudeG Remember that you can always record a macro of putting such a formula in a cell and check auto-generated code. – Egan Wolf Jul 26 '17 at 08:06

score 2 · Answer 2 · answered Jul 26 '17 at 07:55

2

I like using the Chr(34) to get the double " symbol:

Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Formula = "=" & Chr(34) & Chr(34)

answered Jul 26 '17 at 07:55

Shai Rado

33,032
6
29
51

score 1 · Answer 3 · answered Jul 26 '17 at 08:07

1

You probably have to escape quotes, so they are not interpreted as quotes delimiting your string.

For instance, use

Worksheets(name_sheet).Range("A1").Offset(Row - 1, Col - 1).Formula = "="""""

answered Jul 26 '17 at 08:07

sancho.s ReinstateMonicaCellio

14,708
20
93
185

VBA Setting Formula in Range - Problems

3 Answers3