Solved: Find a defined name with VBA

[Edoloto]

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 put it in 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:

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

[p45cal]

I'm not at all clear from your description or from your code whether you're writing to the named cell Charge or from it. Anyway, hopefully one of the two following lines may point you in the right direction:[vba]ThisWorkbook.Sheets("Sheet1").Range("A1") = Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge")
Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge") = "Something"
[/vba]

[Edoloto]

I'm not at all clear from your description or from your code whether you're writing to the named cell Charge or from it. Anyway, hopefully one of the two following lines may point you in the right direction:[vba]ThisWorkbook.Sheets("Sheet1").Range("A1") = Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge")
Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge") = "Something"
[/vba]

Thanks for your reply. I actually want to write to the cell that has a defined name. So I think that the second line of code you wrote will do it ... yes?

Again, thanks a lot.