returning the address of a named range in VBA

i'm trying to return the address of a named range.
ie: if i have a named range called "names" on worksheet "main"

C

4 Adam
5 Ben
6 Michael

I'de like to return "C4:C6"

i can do this on the sheet ADDRESS(ROW(names),COLUMN(names)) but VBA doesn't recognise these commands.

Hello Zinny,

To make your posts easier to read and preserve your formatting, please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
On the Message window Toolbar you will see the # icon. This will automatically wrap the text you selected with the proper HTML tags to create a Code Window in your post. You can do this manually by placing the tag [code] at the start of the line, and the tag [/code] at the end.

As a new member please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...

Forum Rules

Sincerely,
Leith Ross

thanks. i'll have a look into those examples.
i guess more specifically my program works if cellRange is a string "c34:e34"
however i need to refer to a named lists "SUPPLIER_TOTAL_CHARGE" and "TOTAL_PROFIT" which is in the closed worksheet and refers to cell c34 and E34 respectively.
Is there any way to set the value of cellRange so that it refers to a named list instead of entering the range manually?
something like cellRange = Range("TOTAL_SUPPLIER_CHARGE").Address & ":" & Range("TOTAL_PROFIT").Address

Sub GetValuesFromAClosedWorkbook(fPath As String, _
            fName As Variant, sName, cellRange As String, counter As Long)
Dim rownumber As Long

    Debug.Print fPath, fName, sName, cellRange, counter
    rownumber = 4 + counter
 
    
        With ActiveSheet.Range("C" & rownumber & ":E" & rownumber)
          .FormulaArray = "='" & fPath & "\[" & fName & "]" _
                     & sName & "'!" & cellRange _
          .Value = .Value
        End With
  
End Sub

Hi Zinny; it looks like you're decomposing a named range into it's physical address and then using that to refer to the combined range ... which sort of defeats the purpose of a named range.

It also looks like you're trying to use the physical address to write a formula (possibly TOTAL_SUPPLIER_CHARGE minus TOTAL_PROFIT?) .. Do you need the formula in the cell or just the result? If the latter, the simplest way would be:

lngSupplierCosts = range("TOTAL_SUPPLIER_CHARGE").value - range("TOTAL_PROFIT").value

If I've missed the point of what you're trying to do, perhaps a more specific explanation would help?

MM