CODEWITHSUNDEEP
excelvba
A.R.
A.R.

Reputation: 11

Excel VBA: Error 1004 trying to set a value to range

I have a weird bug trying to create a small macro. I want to create a value for a specific cell, that is a function. However when i try to do that, it gives me error 1004.

ThisWorkbook.Sheets("Systems").Range("C" & c.Row).Value = "=CELL(" & Chr(34) & "row" & Chr(34) & ";Articles!A" & c.Value

Articles is just another sheet. The Chr(34) is just a " char.

This line gives me the error. Now, I tried to replace the special characters with Chr commands.

The following line doesn't give me an error

ThisWorkbook.Sheets("Systems").Range("C" & c.Row).Value = Chr(61) & "CELL"

But this one does:

ThisWorkbook.Sheets("Systems").Range("C" & c.Row).Value = Chr(61) & "CELL" & Chr(40)

Chr(61) is a = and Chr(40) is a (.

Can somebody please show me a way to work around this? I would be very glad. Using Ofiice 2010.

Upvotes: 1

Views: 90

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Just double the quotes "" inside a string:

ThisWorkbook.Sheets("Systems").Range("C" & c.Row).Formula "=CELL(""row"",Articles!A" & c.Value & ")"

Also note that you should use .Formula to write a formula in standard US-English format or .FormulaLocal to write a formula in your localization format.

It is recommended to use the standard US format and .Formula as this will work world wide. Standard US format uses , and not ; as separator in formulas. If you have difficulties translating formulas use https://en.excel-translator.de

Upvotes: 1

Related Questions