VB Variable Help!

[Ben W]

Hi,

I have a bit of VB Excel code which uses a formula. For this example
I have a variable (start_distance) which counts the column distance
from Column A, therefore my variable gives me -6.

What I want to do is replace the -6 in the formula with the variable,
therefore if my variable changes then so would the formula. However,
when I replace the -6 in the formula with the variable, I get a
Runtime Error 1004. If I leave the number as -6 it all works fine.

I'm lost here? Do I need to Dim my variable as something other than
double??

Please can someone show me how to write the formula with the variable
in?

Many thanks,

Ben



FORMULA:-

dim start_distance as double

start_distance = 1 - (ActiveCell.Column - 1) - 1
range("G5").select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(RIGHT(RC[-6],5)=""Total"",RC[-2],R[1]C))"
ActiveCell.Select

 

[Trevor Shuttleworth]

Ben

try this:

Sub checkformula()
Range("G5").Select
' start_distance = 1 - (ActiveCell.Column - 1) - 1
' start_distance = -6 if Column = G
' the statement could just as easily be start_distance = -
(ActiveCell.Column - 1)
start_distance = -(ActiveCell.Column - 1)
'=IF(E5="","",IF(RIGHT(A5,5)="Total",E5,G6))
ActiveCell.FormulaR1C1 = _
"=IF(RC[-2]="""","""",IF(RIGHT(RC[" & _
start_distance & _
"],5)=""Total"",RC[-2],R[1]C))"
ActiveCell.Select ' why ??
End Sub

Regards

Trevor

 

[Ben W]

Thanks for your help!