Cell Defined Name problem

[EduardoArias]

Greetings!

Ahhhhh ... I'm just going crazy.

I'm creating a program that takes two values from a spreadsheet (workbook). One of these values is an amount and the other in the name of a workbook.

Once I get these values, I can go to a specific workbook as I have the name. Now, the problem is that the value for the amount I need to write TO a cell that has a defined name called "Charge" in the workbook name I just got.

I can go to the workbook and I've been trying to put it the amount. but my code only works if I leave that cell selected, in other words, if I don't manually take the mouse and click in the cell that has the specific name, my code thinks that there is nothing there,

How can accomplish my goal of finding the cell with the name and put the number there? Now, this cell can be anywhere in the page.

Here is my code:

Code:
Sub FindTheName()
Dim cel As Range, rg As Range
Dim i As Long
Dim temp As Variant
Dim Address As String
Set rg = Worksheets(TemplateName).Range("A:G")
On Error Resume Next
For Each cel In Intersect(ActiveSheet.UsedRange, Columns(ActiveCell.Column))
temp = cel.Name.Name
If Not IsEmpty(temp) Then
Application.Volatile
Address = ActiveCell.Address
ActiveCell.Value = Amount
temp = Nothing
'rg.Cells(i, 1) = temp
'rg.Cells(i, 2) = Range(temp).Address
Else
Err.Clear
End If
'temp = Nothing
Next
Sheets("Allocation Calculations").Activate
End Sub

Thank you,

Eduardo

 

[SkipVought]

Hi,

It is not at all clear, what you are doing. I made some comments, but that's just scratching the surface...

Sub FindTheName()
    Dim cel As Range, rg As Range
    Dim i As Long
    Dim temp As Variant
    Dim Address As String
    Set rg = Worksheets(TemplateName).Range("A:G")
    On Error Resume Next
'I advise against using ActiveSheet & ActiveCell.
'Rather explicitly name the sheet that you expect this range to be on
'  and explicitly reference the Column of interest.
    For Each cel In Intersect(ActiveSheet.UsedRange, Columns(ActiveCell.Column))
'are you saying that each of the cells in this range ALSO has a Range Name?
'or do you mean that the VALUE in each of the cells is a Range Name?
        temp = cel.Name.Name
'temp is a Range Name, so it's just a STRING.
'to reference that range use this syntax instead
'        If Not IsEmpty(temp) Then
        If Not IsEmpty(YourWorkbookObject.Range(temp)) Then
            Application.Volatile
'ActiveCell has not changed and does not change in your code!!!
'you don't do anything with the string, Address!!! ???
            Address = ActiveCell.Address
'where does Amount come from???
            ActiveCell.Value = Amount
'again temp is a STRING, not an object.
            temp = Nothing
            'rg.Cells(i, 1) = temp
            'rg.Cells(i, 2) = Range(temp).Address
        Else
            Err.Clear
        End If
        'temp = Nothing
    Next
    Sheets("Allocation Calculations").Activate
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]