Thread: Cells function within a range

Hi there,

I am currently pulling my hair out over what I thought was a simple piece of code.

Basically I want to access a cell value using the range function. However, I need to access the row and columbn seperately as I am wanting to use activecell.row on the row and then just hardcode in the column.

The line Im using is below but doesnt seem to be giving me bak the answer I want, any idea's?

Code:

MsgBox (wsCD.Range((Cells(ActiveCell.Row, 5))).Value)

wsCD is my sheet I'm working on.

Cheers,

Jag

Thanks for the reply, but it still doesnt seem to work for the purposes I need it.

Howcome when I use it within the following method, it doesnt return a value when I call msgbox?

Code:

Public Sub example1()
Dim wsCD As Worksheet, wsNew As Worksheet
Dim HL As Hyperlink
Dim rngCopy As Range, HLrange As Range
On Error GoTo ExitPoint
Application.ScreenUpdating = False
Set wsCD = Sheets("Compartment Details")
Set wsNew = Sheets.Add(After:=Sheets(Sheets.Count))
For Each HL In wsCD.Hyperlinks
    HLrange = HL.Range
    MsgBox (HLrange.Value) 'tried using the function here to display the hyperlink cell value
    wsCD.Activate: HL.Follow
    Set rngCopy = ActiveCell.Offset(, 1 - ActiveCell.Column).Resize(, 20)
    wsNew.Cells(wsNew.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 20).Value = rngCopy.Value
    Set rngCopy = Nothing
Next HL
ExitPoint:
Set wsCD = Nothing
Set wsNew = Nothing
Application.ScreenUpdating = True
End Sub

Cheers,

Jag