This is too easy to post here but I need help...

Posted by Bill Tanner on February 07, 2002 6:51 PM

Cell Range (Worksheet 1) C49:H49 value is "1567"

VBA Code sez:
If Sheets("Worksheet 1").Range("C49:H49").Value &LT;&GT; "" Then
Sheets("Worksheet2").Select
Range("C68:M68").Select
ActiveCell.FormulaR1C1 = "='Worksheet 1'!C49:H49"
End If

The Code inserts the correct formula but I'm getting a #NAME? and I can't seem to figure out why. I've messed with formatting of the two cells -- trying various combinations of General and Text but other than occassionally changing to a #VALUE error message, I can't seem to make it work.

So what am I doing wrong here?

Thanks in advance.

Bill Tanner

Posted by Voltimand on February 07, 2002 7:16 PM

You cannot have a formula that reads =C49:H49
This produces the error #Value
One cell can only equal one other cell

Is 1567 the total of the range C49:H49 ?

Posted by George on February 07, 2002 7:31 PM

Try this...

Public Sub InsertFormula()
Dim shtName As Variant
Application.Worksheets("SHEET2").Activate
shtName = ActiveSheet.Name
Application.Worksheets("SHEET1").Activate
Range("B2").Select
ActiveCell.Value = "=" & shtName & "!C49"
ActiveCell.Copy
'Copy the link to the cells to the right
Range(ActiveCell, ActiveCell.Offset(0,5)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Posted by Bill Tanner on February 07, 2002 7:36 PM

You're right -- the formula reads =C49. The =C49:H49 was one of my attempts to fix and is the one that pulled up the #Value message. Taking out the :H49 returns me to the #Name? message.

The C49:H49 range referred to cells that had been merged on a worksheet. The worksheet is being used as an informal input form and the entries extend beyond the visual boundaries of a single cell.