CODEWITHSUNDEEP
vbaexcelexcel-formula
Claude G
Claude G

Reputation: 23

VBA Setting Formula in Range - Problems

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

Upvotes: 1

Views: 108

Answers (3)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

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 = "="""""

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33672

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)

Upvotes: 2

Egan Wolf
Egan Wolf

Reputation: 3573

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

Try this:

"="""""

Upvotes: 2

Related Questions