PDA

View Full Version : Solved: Find a defined name with VBA



Edoloto
10-20-2008, 01:01 PM
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 :dunno

p45cal
10-20-2008, 02:20 PM
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:ThisWorkbook.Sheets("Sheet1").Range("A1") = Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge")
Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge") = "Something"

Edoloto
10-20-2008, 02:38 PM
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:ThisWorkbook.Sheets("Sheet1").Range("A1") = Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge")
Workbooks("Book20.xls").Sheets("Sheet2").Range("Charge") = "Something"

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.