How to take cell with custom format and assign to variable as text (as appears in cell)

Hello,

I have a cell 'A1' with a custom format. The value typed in the cell is '58' but the custom format forces it to appear as 'R00058'.

How would you assign 'R00058' to a variable which will be later used for other functions?

Using the following code as a test I am assigning Range("A1") to a variable and pasting it in cell 'A5'. This does not work as desired because all it's doing is copying the cell with the format applied.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim myData As Range
    Set myData = Range("A1")
    myData.Copy
    Range("A5").Select
    ActiveSheet.Paste
End Sub

I have tried replacing the second line in my code with the following but a Run-time Error '424' is the result.

Set myData = Range("A1").Text

Also, when is it necessary to use 'Set'? I have also searched and found limited use of 'Let'.

Getting rid of 'Set' in that line of code results in an Run-time error '91': Object variable or With block variable not set.


Thanks for any help.

TV

Not exactly sure what you want to do but this would put the value from the cell as it's formatted into the variable.

myData = Range("A1").Text