Array formula problem in VB

[Paul]

Hi,

I'm trying to make a spreadsheet wherby formulas are automatically
entered in column A, dependent on data being in column B.

Brad helped me out with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If IsEmpty(Target.Value) Then
Target.Offset(0, -1).Clear
Else: Target.Offset(0, -1).Formula = "=(b1*2)"
End If
End If
End Sub

This works like a charm, but I fogot to mention in my original post
that I would like the formula entered to be: b1 in a1, b2 in
a2,...,b99 in a99 , possibly an array formula.

Any help would be greatly appreciated.

Paul

 

[Frank Kabel]

Hi
try replacing the line
Else: Target.Offset(0, -1).Formula = "=(b1*2)"
with
Else: Target.Offset(0, -1).FormulaR1C1="=R[0]C[1]*2"

 

[Paul Browne]

Thanks frank,

Again, that works perfectly.

Many thanks.

Paul

 

[Paul Browne]

At the risk of pushing my luck..

The below formlua works great:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If IsEmpty(Target.Value) Then
Target.Offset(0, -1).Clear
Else: Target.Offset(0, -1).FormulaR1C1 = "=R[0]C[1]*2"
End If
End If
End Sub

But can i be so bold as to ask what syntax i could use if my target
column is on another worksheet (i.e. sheet2 column b).

Paul

 

[Frank Kabel]

Hi Paul
not quite what formula you want but you may try
Target.Offset(0, -1).FormulaR1C1 = "='sheet2'!R[0]C[1]*2"

 

[Paul Browne]

Thanks for the responses today to Frank.

That will evaluate the values based on the values in the second sheet
fine, but won't clear the formulas as the values in the second sheet are
empty.

I think i need the

If Target.Column = 2

line to refer to the column in the second sheet...ie

if target.column ='sheet2!'2 kindof thing....


god i'm crap at excel, i think i'll leave it...i'm going mad.

 

[Frank Kabel]

Hi
try
Target.Offset(0, -1).FormulaR1C1 =
"=IF('sheet2'!R[0]C[1]<>"""",'sheet2'!R[0]C[1]*2,"""")"