Reporting Value of Cell

[GreenthumbUK]

Hello,

I want to pick up a peace of text from a named range "Group_Reference", then drop it in another cell.
Instead the below code is reporting Group_Reference as text instead of the text entered in Group_Reference, any ideas.



Sub group_Reference()
Dim group_Reference As Range

Application.ScreenUpdating = False
Application.Run "Unlock_WorkshLS"
Sheets("WorkshLS.XLS").Select
Range("A600").Select
ActiveCell.FormulaR1C1 = "group_Reference"
Application.Run "Lock_WorkshLS"

Application.ScreenUpdating = True
End Sub

 

[SkipVought]

hi green,

This statement
[tt]
ActiveCell.FormulaR1C1 = "group_Reference"
[/tt]
assigns "group_Reference" to the VALUE of the cell.

If your intention is to NAME the RANGE reference "group_Reference" then

    ActiveWorkbook.Names.Add _
      Name:="group_Reference", _
      RefersTo:="='" & ActiveSheet.Name & "'!" & [A600].Address

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[GreenthumbUK]

Thanks for that, I have already named a cell group refence, this cell contains a variable string which I want to pick up and drop in cell A600.

I could do copy/paste but I would like to use this method.

any ideas?

 

[SkipVought]

ActiveCell.Value= Range("group_Reference").Value

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

You do NOT have to Select A600! Better not to -- faq707-4105 How Can I Make My Code Run Faster?

[A600].Value= Range("group_Reference").Value

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[GreenthumbUK]

Spot on Skip, thanks.......

 

[xlbo]

Just to be pedantic (and make it shorter) - if we're using the evaluate method, it works on range names as well:

[A600]= [group_Reference]

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question