I am trying to build a sub in Excel vba to create a formula from values
on another sheet.

The code is

Sub Createlinks()
'
' Build formula for summary of weekly accom into main ssheet

'

Dim col1, col2 As Integer


col1 = -23
col2 = col1 + 6


For x = 1 To 35

ActiveCell.FormulaR1C1 = "='A1'!A2 - MAX('A1'!R[-32]C[" & col1 &
"]:R[-32]C[" & col2 & "])"
ActiveCell.Offset(0, 1).Range("A1").Select

col1 = col2
col2 = col1 + 6

Next x


End Sub

All seems to work fine except that the "='A1'!A2 element appears on
the spreadsheet as "='A1'!'A2'
adding in ' ' around A2, this then generates an invalid name range. ??

Why are the extra ' ' being added ?? tried using a string to add in the
"='A1'!A2 element but still i have the same error.

?? Any help gratefully recieved

Mark

Dusting off the cobwebs of VBA knowledge, seem to have forgotten more
than I once knew !!

Setting the FormulaR1C1, requires a cell reference in that notation, try
this....

ActiveCell.FormulaR1C1 = "='A1'!R2C1 & - MAX('A1'!R[-32]C[" & col1 &
"]:R[-32]C[" & col2 & "])"


--
Cheers
Nigel



"lensteruk" wrote in message
ups.com...
I am trying to build a sub in Excel vba to create a formula from values
on another sheet.

The code is

Sub Createlinks()
'
' Build formula for summary of weekly accom into main ssheet

'

Dim col1, col2 As Integer


col1 = -23
col2 = col1 + 6


For x = 1 To 35

ActiveCell.FormulaR1C1 = "='A1'!A2 - MAX('A1'!R[-32]C[" & col1 &
"]:R[-32]C[" & col2 & "])"
ActiveCell.Offset(0, 1).Range("A1").Select

col1 = col2
col2 = col1 + 6

Next x


End Sub

All seems to work fine except that the "='A1'!A2 element appears on
the spreadsheet as "='A1'!'A2'
adding in ' ' around A2, this then generates an invalid name range. ??

Why are the extra ' ' being added ?? tried using a string to add in the
"='A1'!A2 element but still i have the same error.

?? Any help gratefully recieved

Mark

Dusting off the cobwebs of VBA knowledge, seem to have forgotten more
than I once knew !!

Nigel you are a star, needed to change a few things and put in a
variable which keeps the reference to the static element but got it
sussed now. Many thanks indeed

Mark

Nigel wrote:
Setting the FormulaR1C1, requires a cell reference in that notation, try
this....

ActiveCell.FormulaR1C1 = "='A1'!R2C1 & - MAX('A1'!R[-32]C[" & col1 &
"]:R[-32]C[" & col2 & "])"


--
Cheers
Nigel



"lensteruk" wrote in message
ups.com...
I am trying to build a sub in Excel vba to create a formula from values
on another sheet.

The code is

Sub Createlinks()
'
' Build formula for summary of weekly accom into main ssheet

'

Dim col1, col2 As Integer


col1 = -23
col2 = col1 + 6


For x = 1 To 35

ActiveCell.FormulaR1C1 = "='A1'!A2 - MAX('A1'!R[-32]C[" & col1 &
"]:R[-32]C[" & col2 & "])"
ActiveCell.Offset(0, 1).Range("A1").Select

col1 = col2
col2 = col1 + 6

Next x


End Sub

All seems to work fine except that the "='A1'!A2 element appears on
the spreadsheet as "='A1'!'A2'
adding in ' ' around A2, this then generates an invalid name range. ??

Why are the extra ' ' being added ?? tried using a string to add in the
"='A1'!A2 element but still i have the same error.

?? Any help gratefully recieved

Mark

Dusting off the cobwebs of VBA knowledge, seem to have forgotten more
than I once knew !!