help with VBA formulas R1C1

I have not tested this but if it doesn't work I would be happy to fix it if you attach your file.

Don't use smart quotes (“ ”) in code. Those won't work in VBA and make the code hard to read here.

Please Login or Register  to view this content.

In R1C1 notation, if you omit the number for row/column it means "same row/column that the formula is in".

First, I would suggest something like this to become familiar with R1C1 notation: https://support.office.com/en-ca/art...es_in_formulas

Second, I note that the statement that concatenates your formula (Curcell.formula="=R1C1&"" ""&R1C2") is for the .formula property, which uses A1 notation. If you want to use R1C1 notation, you need to use the .formulaR1C1 property. https://msdn.microsoft.com/EN-US/lib.../ff823188.aspx

I also note in this statement, that you have put the concatenate & operate inside of the quotes, which makes it part of the text string, with no operator to tell VBA to put those strings together. You need to become more familiar with text manipulation and concatenation. I expect it should be something like "R1C1" & """ """ & "R1C2". Because you are trying to include a space (with its accompanying quotation marks in the formula), you will probably need to use multiple quotation marks, and I have never really had to deal with "quotes within text strings" like this, so I may not have got the number of quotation marks right.

I also note that you are using absolute references in your R1C1 reference. Your A1 "equivalent" is using relative references. Either is syntactically correct, but you need to know which you intend.

Finally, you are looping through each cell individually and writing the formula in each cell. One of the big advantages of R1C1 notation for this sort of thing is that the formula text string will not change across the row. This means that you can put the formula string together once, then paste the formula string into the entire range in one step.

Please Login or Register  to view this content.

@Mr. Jazzer: Thanks. Didn't know that about omitting the number. Good to know. What do you mean by smart code? I thought I was just putting the quotes in quotes.

@ Mr. Shorty. I'll read that link. Thanks. Why do you have to use formula.R1C1? Mr Jazzer is not doing that. Yes, I mean to use relative references. Mr Jazzer let me know that if you omit the number it turns into a relative reference. I decided to just go with A1 notation. Ah, interesting about the non-changing formula. I remember reading that. Thanks!!

I ended up just doing this and it worked. I guess the formula moves along with the loop.

Please Login or Register  to view this content.

I helped you out with the code tags because you used them in your first post so I assume this is an oversight. But please use them, it's a rule. -6SJ

You shouldn't need to loop.

If you wanted the same formula, with the same references, in all cells in the row you can use absolute references.

Please Login or Register  to view this content.

You don't need a loop@

Please Login or Register  to view this content.

Regards, TMS

@ Mr. Shorty. I'll read that link. Thanks. Why do you have to use formula.R1C1? Mr Jazzer is not doing that.

I stand (somewhat) corrected. I did not know, nor have I seen any documentation that describes the .formula property automatically "reading" R1C1 notation, but it appears to "work". I would still be cautious using R1C1 notation with the .formula property, because of the potential for ambiguity. Without testing, how does the .formula property interpret "=RC1 & RC2"? In R1C1 notation, RC1 refers to this row column 1. In A1 notation, RC1 refers to column RC row 1. It may work, but I think there is value in making sure that code is "unambiguous", so that Excel/VBA cannot misinterpret something.

All of which is kind of moot, since it sounds like you have decided to go with A1 notation anyway.

I'm glad you seem to have solved your problem. There are a lot of loose ends.

Originally Posted by stephme55

What do you mean by smart code? I thought I was just putting the quotes in quotes.

Here is what I meant by the "smart quotes". That is what Microsoft Word calls them when they curl in. Those are not legal in VBA, you have to use the plain double quotes. I can't copy your code and paste it into VBA without changing the quotes. Your original code:

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

Originally Posted by MrShorty

I expect it should be something like "R1C1" & """ """ & "R1C2". Because you are trying to include a space (with its accompanying quotation marks in the formula), you will probably need to use multiple quotation marks, and I have never really had to deal with "quotes within text strings" like this, so I may not have got the number of quotation marks right.

The original was correct, because he wants the & to be inside the quotes as part of the formula.

Originally Posted by stephme55

I ended up just doing this and it worked. I guess the formula moves along with the loop.

Please Login or Register  to view this content.

If this is what you want then it's very unclear what you were looking for in your original question. You said "I would like it move to the next column as the loop progresses." Didn't you mean that you wanted the formula to from referencing column A, to B, to C, etc.? The solution you give here just puts exactly the same formula everywhere.

Originally Posted by MrShorty

I did not know, nor have I seen any documentation that describes the .formula property automatically "reading" R1C1 notation, but it appears to "work".

Actually I overlooked this and should have used .FormulaR1C1. I am surprised this worked, and even if it does I agree with MrShorty that it is not a good practice.