VBA - to change Formula Reference.

stucamps

Board Regular
Joined
Jul 3, 2003
Messages
114
I am wanting to change the formula reference in a cell dependent on an OptionButton Selected.

Currently the formula in the spreadsheet is:
=IF(AND(ISNUMBER(D12),ISNUMBER(D22)),+D12-D22,"-")

but if I select an option button then I want to change the D12 to D13, as I want to select a different row for analysis, and I have tried the following code:
Code:
Private Sub cmdConfirm_Click()
If OptionButton1.Value = True Then
ThisWorkbook.Sheets(2).Range("d22").FormulaArray = "=IF(AND(ISNUMBER(D12), ISNUMBER(D22)),+D12-D22," - ")"

End If

End Sub
I even tried to change the formula in VBA to
ThisWorkbook.Sheets(2).Range("d22").FormulaArray = "=IF(AND(ISNUMBER(R13C4), ISNUMBER(R22C4)),+R13C4-R22C4," - ")"

Please note that D22 does not need to change
using the RxCx method but to no avail.
Any ideas on how I can change this?
Thanks
Stuart
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
Have you tried using .Formula instead of .FormulaArray ?

eg;

Code:
Cells(1, 1) = 105.36
Range("A2").Formula = "=A1/2.36"
 

stucamps

Board Regular
Joined
Jul 3, 2003
Messages
114
Thanks but that didnt solve anything either just a type mismatch error.
 

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
The bit it is objecting to is the " - " symbol at the end of the formula. If you change this to a number (eg: 0 ) the formula works and is pasted. If you leave it as blanl (eg: "") or as you have it " - " is throws a spanner.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
That's not an array formula, but that wasn't the problem --

ThisWorkbook.Sheets(2).Range("d22").Formula = "=IF(AND(ISNUMBER(R13C4), ISNUMBER(R22C4)),R13C4-R22C4," & """" & " - " & """" & ")"


Need a lotta quotes to get a real one ...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,144
Members
412,306
Latest member
fabio6
Top