CODEWITHSUNDEEP
vbaexcel
IIJHFII
IIJHFII

Reputation: 600

Mixing R1C1 and A1 notation

Struggling through applying a normal excel formula into R1C1 format so I can loop the formula in VBA. The question is how can I refer to columns in other sheets using R1C1 notation. So in the following formula:

ell.Offset(0, 5).FormulaR1C1 = "=IF(LEN(R[]C[-2])>0,(R[]C[-2])*VLOOKUP([R[]C[1]),Core!A:C,3,FALSE)/_
VLOOKUP(R[]C[-1],Core!A:C,3,FALSE),R[]C[4])"

How do I get Core!A:C into R1C1 format? I know you can't mix the two formats but i'm afraid i don't know the syntax for this! Any help is appreciated!

EDIT: 

Sub Expo_dos_Formulas()

'===============================================================================
Sheets("Data_Sheet").Activate

For Each cell In Range("G5:G500")
    If cell <> "Error" Then
        cell.Offset(0, 5).FormulaR1C1 = "=IF(LEN(R[]C[-2])>0,_
(R[]C[-2])*VLOOKUP([R[]C[1]),Core!RC1:RC3,3,FALSE)/VLOOKUP(R[]C[-1],_
Core!RC1:RC3,3,FALSE),R[]C[4])"
    End If
Next
'===============================================================================

End Sub

Upvotes: 1

Views: 5240

Answers (1)

Scott Craner
Scott Craner

Reputation: 152515

In place of A:C put C1:C3:

ell.Offset(0, 5).FormulaR1C1 = "=IF(LEN(RC[-2])>0,(RC[-2])*VLOOKUP(RC[1],Core!C1:C3,3,FALSE)/_
VLOOKUP(RC[-1],Core!C1:C3,3,FALSE),RC[4])"

When using R1C1:

  1. the [] will denote a relative non absolute reference. For example RC[-1] refers to the cells directly to the left.

  2. If absolute references are wanted then do not use the [] and it will refer to the row/column number. For example R[1]C3 will return the row directly under the one being referenced but in column C. If the referenced cell is A3 then it would return $C4.

  3. If full column or full row are wanted then discard the R or C. For example C1:C3 would return $A:$C and R[1]:R[2] would return the relative reference of the two full rows directly below the referenced cell

Upvotes: 8

Related Questions