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!