Excel VBA recommendation

1
votes

I am using a formula which will calculate a value using index and match and the output is subtracting from another cell B2 value.I need to keep B2 as common subtracting component across all cell

I am using below formula in cell C2

=IFERROR(B2-(INDEX(raw!$A:$D,MATCH(1,(raw!$A:$A=data!$A2)*(raw!$C:$C=data!C$1),0),4)),"")

enter image description here

But If I drag this formula to next cell, D2 The formula is getting change to 

=IFERROR(C2-(INDEX(raw!$A:$D,MATCH(1,(raw!$A:$A=data!$A2)*(raw!$C:$C=data!D$1),0),4)),"")

C2 is replacing B2

enter image description here

I want to keep B2 as constant

Below is the VBA macro created :

Sub Macro7()
'
' Macro7 Macro
'
    Selection.FormulaArray = _
        "=IFERROR(RC[-1]-(INDEX(raw!C1:C4,MATCH(1,(raw!C1=data!RC1)*(raw!C3=data!R1C),0),4)),"""")"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:D1"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:D1").Select
End Sub

If I manually edit the formula in each cell, it is working fine (changing, C2,D2,E3,F2 etc to B2). But I am using this formula in 100s of cells and manually editing all is not practically possible. Is there anyway I can change the VBA to keep B2 as constant across the range of cell to run this formula ?

2
vbaexcelexcel-formula
why don't you use =IFERROR($B$2-... – user2140173

2 Answers

4
votes

I think you are looking for the anchor cell in a formula feature in Excel. This can be done by adding a "$" dollar sign in the formula. So change "B2" to "$B$2" and that should keep B2 anchored when you copy your formula.

I'm not sure how or if this will convert to VBA well, but I figured this might point you in the right direction.

0
votes

I think your question has already been answered, but that I have something to add to your understanding...

If you want to replicate the semi-static $B2 reference when entering a formula using the R1C1 reference style, you should change

=IFERROR(RC[-1]...

to

=IFERROR(RC2...

The RC[-1] in the first example places a dynamic reference to the same row and 1 column to the left, whereas RC2 places a semi-static reference to the same row and always the 2nd column.

What confuses me is that this whole line has a mix of R1C1 references and standard references:

"=IFERROR(RC[-1]-(INDEX(raw!C1:C4,MATCH(1,(raw!C1=data!RC1)*(raw!C3=data!R1C),0),4)),"""")"