CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-r1c1-notation
user56591
user56591

Reputation: 503

Excel VBA: Formula R1C1 with absolute reference to multiple columns from variables

I have the following issue:

I am getting various Excel sheets with more or less the same headers (they are not always the same), but almost always in a different order. However, I can get the index of the column by searching for the header name. What I'm trying to do now is to create a new column which is basically a concatenation of every row in the columns of which I have the indexes. I'm trying to do that by using the R1C1 formula, but I can't get it to work properly.

Example Table:

Name | Surname | Nationality | Destination
------------------------------------------
Sue  | Ohara   | American    | Spain
Jon  | Miller  | British     | Italy

Now I want a new column Information:

Name | Surname | Nationality | Destination | Information
-------------------------------------------------------------------------
Sue  | Ohara   | American    | Spain       | Sue, Ohara, American (Spain)
Jon  | Miller  | British     | Italy       | Jon, Miller, British (Italy)

I have the index of the needed columns in four different variables: c1, c2, c3 and c4.

I read in this post that it is possible to use variables in order to refer to absolute columns in the R1C1 formula, however, when I try to do the following, I keep getting errors:

Range(Cells(2, lastCol).Address, Cells(lastRow, lastCol).Address.FormulaR1C1 = "=C"&c1 & C & c2

Why does this not work?

However, this works

Range(Cells(2, lastCol).Address, Cells(lastRow, lastCol).Address.FormulaR1C1 = "=C"&c1

And why can't I simply write something like:

Range(Cells(2, lastCol).Address, Cells(lastRow, lastCol).Address.FormulaR1C1 = "=C" & c1 & "," & C & c2 & " - (" & C & c3 & ")"

Can anyone please help me here? Any help is as always very much appreciated!

Upvotes: 0

Views: 856

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

Try this:

Sub test1()
    Dim c1, c2, c3, c4, lastCol, lastRow
    c1 = 5: c2 = 7: c3 = 12: c4 = 17: lastCol = 20: lastRow = 10 ' dummy values
    
    
    Range(Cells(2, lastCol), Cells(lastRow, lastCol)).FormulaR1C1 = _
        "=RC" & c1 & "&RC" & c2 & "&RC" & c3 & "&RC" & c4
End Sub

Upvotes: 1

Related Questions