0

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

Improve this question
9
  • 1
    Do you still need the formula or just the value is fine? Commented Aug 16, 2021 at 14:47
  • 1
    The problem is that & i & falls within the quotes. Variables (and operators) belong outside quotes. Actually your quotes are off in several places. Commented Aug 16, 2021 at 14:48
  • 1
    Hey Raymond, not 100% sure what you mean, but I need the value to be put into the new cell. Commented Aug 16, 2021 at 14:49
  • 2
    You don't need a loop to do this either btw. But I think "='Nye_Virksomheder'!R" & i & "C2 & ""("" & 'Nye_Virksomheder'!R" & i & "C4 &"")""". Commented Aug 16, 2021 at 14:52
  • 2
    Thank you soo much Bigben. That worked. Also thanks to you Raymond!! Commented Aug 16, 2021 at 14:54

1 Answer 1

Reset to default
0

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.