ActiveCell.Formula Syntax

[Notorious_F.A.T]

This will be a no-brainer for most, but I am stumped.

I am trying to have an ActiveCell.Formula that will spit out th
following:

The macro goes off to a cell that contains a mortgage number and the
stores the Cell Address as X.

Then it goes off to another cell and is supposed to say:
ActiveCell.Formula = "=Mortgage Number: " and then X, so that it refer
to that first active cell's address.

Here's what I have so far:
ActiveCell.FormulaR1C1 = "=""Mortgage Number: """

I've tried ActiveCell.FormulaR1C1 = X, and that displays the address o
the first cell as the contents, which is fine, but I need to includ
the constant "="MortgageNumber: "" in front of that.

Hope that makes sense.

Thx

 

[Rob van Gelder]

Not 100% sure I actually understand what you want.

I've commented out the first line, just in case you don't really want R1C1
style

Sub test()
' ActiveCell.FormulaR1C1 = _
"=""Mortgage Number: "" & " & _
Range("MortgageNo").Address(False, False, xlR1C1)
ActiveCell.Formula = _
"=""Mortgage Number: "" & " & _
Range("MortgageNo").Address(False, False)
End Sub

 

[Bob Phillips]

If you are looking to find the cell with a MN, and you want to reference a
MN sheet, try this version

Dim oCell As Range

On Error Resume Next
Set oCell = Cells.Find("abc #1")
If Not oCell Is Nothing Then
ActiveCell.FormulaR1C1 = _
"='Mortgage Number'!" & _
oCell.Address(False, False)
End If
On Error GoTo 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Not 100% sure I actually understand what you want.

I've commented out the first line, just in case you don't really want R1C1
style

Sub test()
' ActiveCell.FormulaR1C1 = _
"=""Mortgage Number: "" & " & _
Range("MortgageNo").Address(False, False, xlR1C1)
ActiveCell.Formula = _
"=""Mortgage Number: "" & " & _
Range("MortgageNo").Address(False, False)
End Sub