Shortcut to a cell

[DChambers]

Is there a way to reference a cell in a worksheet in a workbook without
having to do the following each time I need access to the cells data?

If Workbook(sReference).Worksheets(sAMS).Range("A" & sAMSRPTR, sAMSLastCol & aAMSRPTR) = "hello" then
   Workbook(sReference).Worksheets(sAMS).Range("A" & sAMSRPTR, sAMSLastCol & aAMSRPTR) = "Hi"
Else
Workbook(sReference).Worksheets(sAMS).Range("A" & sAMSRPTR, sAMSLastCol & aAMSRPTR) = "Goodby"
End If

Would like to assign it to something like RefCell so that I could extract, modify then replace the cells contents.

ex:

If RefCell = "Hello" then
RefCell = "Hi"
Else
RefCell = "Goodby"
End If

 

[VoG]

Create a named range for the cell - with the cell selected type in the Address bar RefCell and Press Enter. Then you can use code like

Sub hello()
If Range("RefCell").Value = "Hello" Then
    Range("RefCell").Value = "Hi"
Else
    Range("RefCell").Value = "Goodbye"
End If
End Sub

Edit: you will need to qualify the range with the workbook name if it is in a different workbook to the calling macro.

 

[DChambers]

Thats it!!

So it would look like:

Dim rRefCell as Range
 
rRefCell = Workbook(sReference).Worksheets(sAMS).Range("A" & sAMSRPTR, sAMSLastCol & aAMSRPTR)

Is the range in the right place?

 

[VoG]

Without seeing more of the code it is difficult to advise but what about

Set rRefCell = Workbook(sReference).Range("RefCell")