Use a Range variable in FormulaR1C1

[citroendealer]

Why does the following code results in a error?
(The code is a simplified version of my code, in reality I want a much bigger formula.)

Sub test()


Dim a As Range


Set a = Selection
Range("A1").Select
ActiveCell.FormulaR1C1 = "=MAX(" & a & ")"


End Sub

 

[sheetspread]

Try:

Sub test()
Dim a As Range
Set a = Selection
Range("A1").Formula = "=MAX(" & a.Address & ")"
End Sub

 

[citroendealer]

This works but now I can't use the R1C1 notation. I already explained that my formula is much bigger...

ActiveCell.FormulaR1C1 = "=IF(MAX(-MIN(" & psrange & ");MAX(" & psrange & "))<" & maxps & "*0,1;1;IF(ABS(RC[-1])>ABS(RC[-2])*2;0;IF(ABS(RC[-2])>ABS(RC[-1])*2;0;1)))"

 

[sheetspread]

This?

Sub test()
Dim a As Range
Set a = Selection
Range("A1").FormulaR1C1 = "=MAX(" & a.Address(ReferenceStyle:=xlR1C1) & ")"
End Sub

If it's too much to write each time and you must use R1C1 for whatever reason, variable a can be a string with the address.

 

[citroendealer]

Thx, it is indeed a lot of work. But I need to do it one time so no problem.

 

[citroendealer]

I don't understand what is happening with my code. See code below:
line 1: is the same code you provided 'Sheetspread' and it works!
line 2: this line gives an error but I didn't do anything different...
line 3: this line works without the equals (=) sign, when I put the equal sign in front of the formula in the spreadsheet it works. But when I add the equal sign to the code it doesn't work...

The range a is two fields with a positive and a negative number and I need the maximal absolute value of these two.

Sub test()


Dim a As Range
Set a = Selection
'line 1
Range("A1").FormulaR1C1 = "=MAX(" & a.Address(ReferenceStyle:=xlR1C1) & ")"
'line 2
Range("A1").FormulaR1C1 = "MAX(-MIN(" & a.Address(ReferenceStyle:=xlR1C1) & ");MAX(" & a.Address(ReferenceStyle:=xlR1C1) & "))"
'line 3
Range("A1").FormulaR1C1 = "MAX(-MIN(" & a.Address & ");MAX(" & a.Address & "))"


End Sub

 

[MARK858]

Assuming your comma separator is right for your region then possibly....

Sub test()


Dim a As Range
Set a = Selection
'line 1
Range("A1").FormulaR1C1 = "=MAX(" & a.Address(ReferenceStyle:=xlR1C1) & ")"
'line 2
Range("A1").FormulaR1C1 = "=MAX(-MIN(" & a.Address(ReferenceStyle:=xlR1C1) & ");MAX(" & a.Address(ReferenceStyle:=xlR1C1) & "))"



End Sub

The above is just changing your syntax, I haven't tested your actual formula.

 

[sheetspread]

I don't understand what is happening with my code. See code below:
line 1: is the same code you provided 'Sheetspread' and it works!
line 2: this line gives an error but I didn't do anything different...
line 3: this line works without the equals (=) sign, when I put the equal sign in front of the formula in the spreadsheet it works. But when I add the equal sign to the code it doesn't work...

The range a is two fields with a positive and a negative number and I need the maximal absolute value of these two.

Sub test()


Dim a As Range
Set a = Selection
'line 1
Range("A1").FormulaR1C1 = "=MAX(" & a.Address(ReferenceStyle:=xlR1C1) & ")"
'line 2
Range("A1").FormulaR1C1 = "MAX(-MIN(" & a.Address(ReferenceStyle:=xlR1C1) & ");MAX(" & a.Address(ReferenceStyle:=xlR1C1) & "))"
'line 3
Range("A1").FormulaR1C1 = "MAX(-MIN(" & a.Address & ");MAX(" & a.Address & "))"


End Sub

Here's my test:

Excel 2010
	C	D
4	-7	-1
5	-23	-77
6	-7	-80
7	40	12
8	-88	-37
9	-13	-20
10	42	-14
11	26	-29
12	3	13
13	-11	4
14	-77	-52
15	12	-14
16	-6	-45
17	3	30
18	16	51
19	-22	4
Sheet5

Sub test()


Dim a As Range
Set a = Selection
Range("A1").FormulaR1C1 = "=MAX(" & a.Address(ReferenceStyle:=xlR1C1) & ")"
Range("A2").FormulaR1C1 = "=MAX(-MIN(" & a.Address(ReferenceStyle:=xlR1C1) & "),MAX(" & a.Address(ReferenceStyle:=xlR1C1) & "))"
Range("A3").Value = "=MAX(-MIN(" & a.Address & "),MAX(" & a.Address & "))"


End Sub

Excel 2010
	A
1	51
2	88
3	88
Sheet5

Cell Formulas
Range	Formula
A1	=MAX($C$4:$D$19)
A2	=MAX(-MIN($C$4:$D$19),MAX($C$4:$D$19))
A3	=MAX(-MIN($C$4:$D$19),MAX($C$4:$D$19))

I just changed the ;s to commas (regional) and got rid of the R1C1 in your third formula--you can't mix A1 and R1C1. Mark noticed these too.

 

[citroendealer]

Thx, when replacing ; for , it works fine!