CODEWITHSUNDEEP
vbaexcelexcel-formula
Makah
Makah

Reputation: 4513

Create Formula based on cells

I need to create a Match formula based on the Cells like the example bellow:

+------+--------+--------+
| Col1 | SumCol | Val    | 
+------+--------+--------+
|    1 |      8 | v1     | '=Match([otherWorkbook]MainSheet!C1,RC3,[otherWorkbook]MainSheet!C8)
|    2 |      5 | v44*   | '=Match([otherWorkbook]MainSheet!C2,RC3,[otherWorkbook]MainSheet!C5)
|    3 |      7 | ls*    | '=Match([otherWorkbook]MainSheet!C3,RC3,[otherWorkbook]MainSheet!C7)
+------+--------+--------+

To build the formula I need to get the string "[otherWorkbook]MainSheet" and concatenate with Cell from Column Col1. Then i need to get the value from Column Val. Finally I neet to concatenate again the string "[otherWorkbook]MainSheet" with cell from Column SumCol.

I expect a formula like =Match([otherWorkbook]MainSheet!C1,RC3,[otherWorkbook]MainSheet!C8). I could make it using two temp Columns to make the concatenation and then, build the wole formula.

PS: I'm using VBA to make the formula, so i can use it to reach my goal.

Upvotes: 0

Views: 185

Answers (1)

Bernard Saucier
Bernard Saucier

Reputation: 2270

Maybe you need something like this : 

Sub AddFormulas()

    myOtherWorkbook = "[otherWorkbook]"

    myFormula1 = "=Match(" & myOtherWorkbook & "MainSheet!C"
    myFormula2 = ",RC3," & myOtherWorkbook & "MainSheet!C"

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For x = 2 To lastRow  'Loops from row 2 to the last of column "A"

        Cells(x, 4).Formula = myFormula1 & Cells(x, 1) & myFormula2 & Cells(x, 2) & ")"

    Next x


End Sub

Upvotes: 1

Related Questions