Convert Range to String

[erice]

Hello,

I am trying to take the number that appears in the cell Range(B1:C1) and have it used in a string. B1:C1 is a merged cell that I cannot change in the sheet. Eventually I want to use the string as the subject line of an email, but for this time I am having it appear in a message box.

Sub Send_Update()

Dim rng As Range
Dim EPR As String

Set rng = Range("B1:C1")
EPR = rng.Address(RowAbsolute, ColumnAbsolute)
MsgBox (EPR & " was updated")
  

End Sub>

[xlnitwit]

If you want the cell content, you can just use

MsgBox Range("B1").Value & " was updated"

[gmr4evr1]

Or ..

Sub Send_Update()

Dim rng As Range
Dim EPR As String

Set rng = Range("B1")
EPR = rng.Value
MsgBox (EPR & " was updated")
  

End Sub

Since B1:C1 are merged cells, you only have to use the value from B1
Then again, xlnitwit's solution is much easier.

[queuesef]

Why do you need Merged cell/wrapped cell in B1:C1 ? I would eliminate it. Create a simple formula/function: mySt = sheet1.Range("B1") - You can verify it in immediate window ( debug.Print cstr(myst) ) or use Cstr( ) .

[erice]

Thank you all for the responses. Each one of these solutions seemed to work. I would like to get rid of the merged cell, but the actual sheet that this is going in (not the one attached) was one that I inherited and I cannot change it without fixing a lot of editing the other formulas and Macros.

Thanks again!

[gmr4evr1]

You're welcome.