Formula to Cell issue

[Ampleford]

I'm trying to add a formula to a cell using VB and it keeps stopping, but I can't see where I'm going wrong - can someone maybe help me? I'd like the following formula in Cell BA2 on the tab "Theo"

=IF(SUM(AC2:AD2)=0,"XX",AZ2&"~"&SUM(AC2:AD2)&"-"&Y2) so I'm using the following:-

Sub CalcsToData()
Sheets("Theo").Select
Range(BA2).Value = "=IF(SUM(AC2:AD2)=0,Chr34XXChr34,AZ2&Chr34~Chr34&SUM(AC2:AD2)&Chr34-Chr34&Y2)"
End Sub

But I'm getting the following:-

Run-time error '1004':
Method 'Range' of object '_Global' failed

Can anyone assist?

 

[maabadi]

If you want See formula Try Method 1 AND if you want only Result Try Method 2.
Method 1:

Sub Macro1()
Sheets("Theo").Select
Range(BA2).FormulaR1C1 = "=IF(SUM(RC[-24]:RC[-23])=0,""XX"",RC[-1]&""~""&SUM(RC[-24]:RC[-23])&""-""&RC[-28])"
End Sub

Method 2

Sub Macro2()
Sheets("Theo").Select
If Application.WorksheetFunction.Sum(Range("AC2:AD2")) = 0 Then
Range(BA2).Value = "XX"
Else
Range(BA2).Value = Range("AZ2").Value & "~" & Application.WorksheetFunction.Sum(Range("AC2:AD2")) & "-" & Range("Y2").Value
End If
End Sub

 

[FormR]

Hi, another option.

Sheets("Theo").Range("BA2").Formula= "=IF(SUM(AC2:AD2)=0,""XX"",AZ2&""~""&SUM(AC2:AD2)&""-""&Y2)"

 

[Ampleford]

If you want See formula Try Method 1 AND if you want only Result Try Method 2.
Method 1:

Sub Macro1()
Sheets("Theo").Select
Range(BA2).FormulaR1C1 = "=IF(SUM(RC[-24]:RC[-23])=0,""XX"",RC[-1]&""~""&SUM(RC[-24]:RC[-23])&""-""&RC[-28])"
End Sub

Method 2

Sub Macro2()
Sheets("Theo").Select
If Application.WorksheetFunction.Sum(Range("AC2:AD2")) = 0 Then
Range(BA2).Value = "XX"
Else
Range(BA2).Value = Range("AZ2").Value & "~" & Application.WorksheetFunction.Sum(Range("AC2:AD2")) & "-" & Range("Y2").Value
End If
End Sub

I'm going to copy the formula down, so using Method 1, but I'm still getting the same message:-

 

[Ampleford]

Hi, another option.

Sheets("Theo").Range("BA2").Formula= "=IF(SUM(AC2:AD2)=0,""XX"",AZ2&""~""&SUM(AC2:AD2)&""-""&Y2)"

This has worked... Thank you so much

 

[maabadi]

Sorry my fault:
Method1

 Sub Macro1() 
Sheets("Theo").Select 
Range("BA2").FormulaR1C1 = "=IF(SUM(RC[-24]:RC[-23])=0,""XX"",RC[-1]&""~""&SUM(RC[-24]:RC[-23])&""-""&RC[-28])"
 End Sub

Method 2

 Sub Macro2() 
Sheets("Theo").Select
 If Application.WorksheetFunction.Sum(Range("AC2:AD2")) = 0 Then
 Range("BA2").Value = "XX" 
Else
 Range("BA2").Value = Range("AZ2").Value & "~" & Application.WorksheetFunction.Sum(Range("AC2:AD2")) & "-" & Range("Y2").Value
 End If 
End Sub

 

[FormR]

I'm going to copy the formula down

HI, if you intend to do this programatically, then instead of entering the formula in a single cell and copying it down, you can do it all in one step. For example.

Sheets("Theo").Range("BA2:BA10").Formula = "=IF(SUM(AC2:AD2)=0,""XX"",AZ2&""~""&SUM(AC2:AD2)&""-""&Y2)"

 

[Ampleford]

HI, if you intend to do this programatically, then instead of entering the formula in a single cell and copying it down, you can do it all in one step. For example.

Sheets("Theo").Range("BA2:BA10").Formula = "=IF(SUM(AC2:AD2)=0,""XX"",AZ2&""~""&SUM(AC2:AD2)&""-""&Y2)"

That would be even gooder, but the range is going to be dynamic, in that there will be an unknown number of rows in each iteration of the sheet as we're reconciling a set of journals which can vary in quantity each month, but thank you very much for that , and I'm sure I will be able to use that in future

 

[Ampleford]

Sorry my fault:
Method1

 Sub Macro1()
Sheets("Theo").Select
Range("BA2").FormulaR1C1 = "=IF(SUM(RC[-24]:RC[-23])=0,""XX"",RC[-1]&""~""&SUM(RC[-24]:RC[-23])&""-""&RC[-28])"
End Sub

Method 2

 Sub Macro2()
Sheets("Theo").Select
If Application.WorksheetFunction.Sum(Range("AC2:AD2")) = 0 Then
Range("BA2").Value = "XX"
Else
Range("BA2").Value = Range("AZ2").Value & "~" & Application.WorksheetFunction.Sum(Range("AC2:AD2")) & "-" & Range("Y2").Value
End If
End Sub

Thanks very much for this. It's appreciated...