.range

hi there
I need to display the data from a list of named cells (NameCells) when there is a value for that specific cell.

here is my code

VB Code:

Private Sub PrintTest()
    Dim xlApp As Object
    Dim xlWkBk As Object
    Dim xlSheet As Object
    Dim filePath As String
    
    Dim NameVal As String
    Dim DescVal As String
    Dim DeptVal As String
    Dim Purpose As String
        
    ' set values
    NameVal = "AirCon1"
    DescVal = " Air Con"
    DeptVal = "IT"
    PurposeVal = ""
    
    filePath = "c:\Test Excel\Test.xls"
    Set xlApp = CreateObject("Excel.Application")
    Set xlWkBk = xlApp.Workbooks.Open(filePath)
    Set xlSheet = xlWkBk.Worksheets(1)
    
    With xlSheet
    
        
        If NameVal <> Null Then
             
            '.Range(get from the NamedCell).Value = NameVal
            
         End If
     
        ' and so on
     
     End With
    xlWkBk.PrintOut
    xlWkBk.Close SaveChanges:=False
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlWkBk = Nothing
    Set xlApp = Nothing
    
End Sub
 
Private Sub NamedCell(TemplatePath As String, _
                                Name As String, _
                                Desc As String, _
                                Dept As String, _
                                Purpose As String)
 
                                
Const NamedCell_Name As String = "Name"
Const NamedCell_Desc As String = " Desc"
Const NamedCell_Dept As String = " Dept"
Const NamedCell_Purpose As String = " Purpose"
 
End Sub

any help please

Moved to Office Development

If I understand you correctly your looking for the range of a named range?

VB Code:

'Range "Test" is from A1 to B5 on sheet1
Debug.Print Workbooks(1).Sheets(1).Range("Test").Name
'=Sheet1!$A$1:$B$5

i have already named the cells in my test.xls, if the system does not return a value of a specific field for example 'purpose', then i should only get the named cells that is the name, description and department from the sub NamedCell.

Sorry, I dont quite follow you.

i know its not clear. the situation is i need to get the named cells from a sub which contains a list of defined named cells when there are data for them. For example,

'for testing
NameVal = "AirCon1"
DescVal = " Air Con"
DeptVal = "IT"
PurposeVal = ""

therefore on my excel report i'm going to have the name , description and department values printed but not 'purpose' because it has no value. This is just a concept i'm testing which will be then implemented in a real world application which will have more than 20 named cells.

please help

Prav:

I think your "Null" test is your problem. The definition I have for "Null" is:

Null indicates that no space is reserved in memory for the variable.

Here is some code to look at with 3 different tests. Play with it and try to understand how to get all 6 messages to occur (hint: look at the definition of Null closely ... to get the "NOT Null" message you'll have to move some code around!):

Code:

---

Option Explicit
Sub Macro1()
Dim aRange As Range

'Set a Range variable to refer to a named cell
Set aRange = Range("CellC3")  '< NameOf $C$3 was set to "CellC3"
aRange.Select  'Unnecessary - just show that correct cell is loaded in variable
'
'Three different test blocks
If IsNull(aRange) Then
    MsgBox "NULL"
Else
    MsgBox "NOT Null"
End If
   
If IsEmpty(aRange) Then
    MsgBox "IS EMPTY"
Else
    MsgBox "is NOT empty"
End If

If aRange.Value = "" Then
    MsgBox "IS EMPTY STRING"
Else
    MsgBox "is NOT empty string"
End If

End Sub

---

I hope this helps.