Search code examples
excelvbacell

Combining two cells in loop VBA (Excel)

I am trying to combine two cells like this:

Lets say that cell 1 is 123 and cell 2 is 321 Then I want cell 3 to look like this: 123 (321)

So if this was in excel and I wanted to do it with two cells, I would just do it like this:

=E2&"("&F2&")"

But in VBA, I'm actually trying to do it with a loop. So that the i value is the row, and the column is constant.

I've tried to do it like this:

ActiveCell.FormulaR1C1 = _
    "='Nye_Virksomheder'!R & i & C2 & ""(""&'Nye_Virksomheder'!R & i & C4&"")"""

So "Nye_Virksomheder" is the sheet that i am taking the data from.

But I keep getting an error. Not sure what is wrong. Anyone that is able to figure it out?

Kind regards, Zebraboard

Solution

  • You have several issues with quotes.

    "='Nye_Virksomheder'!R" & i & "C2 & ""("" & 'Nye_Virksomheder'!R" & i & "C4 &"")"""

    Note that you don't actually need a loop to write a formula with an increasing row. You can write the formula to the entire range in one go (keeping the row as a relative reference).