Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to loop this macro
This macro allows me to name cells very easily:
Sub NameCells() Dim w As String Dim cell As Range w = Cells(ActiveCell.Row, 1) & "_" & Cells(2, ActiveCell.Column) w = Replace(w, "& ", "") ' several other replacements that I omitted for brevity's sake ThisWorkbook.Names.Add Name:=w, _ RefersTo:=ActiveCell, _ Visible:=True End Sub The trouble is that I have to click the macro in each cell that I want to name. I am using this on financial statements, and I have to do it about 200 times every time I import a new set. Is there any way to loop this so that I can select a range of cells, and it will automatically name all of them based on the above criteria? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to loop this macro
Give this a try. I have replaced ActiveCell with a variable that is
passed as a parameter from a calling function: Sub NameSelected() Dim c As Range For Each c In Selection NameCells c Next End Sub Sub NameCells(r As Range) Dim w As String Dim cell As Range w = Cells(r.Row, 1) & "_" & Cells(2, r.Column) w = Replace(w, "& ", "") ThisWorkbook.Names.Add Name:=w, _ RefersTo:=r, _ Visible:=True End Sub On Jul 30, 2:48*pm, DoooWhat wrote: This macro allows me to name cells very easily: Sub NameCells() Dim w As String Dim cell As Range * * w = Cells(ActiveCell.Row, 1) & "_" & Cells(2, ActiveCell.Column) * * w = Replace(w, "& ", "") * * * * * *' several other replacements that I omitted for brevity's sake * * ThisWorkbook.Names.Add Name:=w, _ * * * * RefersTo:=ActiveCell, _ * * * * Visible:=True End Sub The trouble is that I have to click the macro in each cell that I want to name. *I am using this on financial statements, and I have to do it about 200 times every time I import a new set. *Is there any way to loop this so that I can select a range of cells, and it will automatically name all of them based on the above criteria? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I want to loop this macro
Thank you so much! That worked to perfection.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Discussion (Misc queries) | |||
Loop Macro | Excel Programming | |||
Help with using a Macro Loop | Excel Discussion (Misc queries) | |||
Do until loop with use of another macro in loop | Excel Programming | |||
VBA Macro Loop | Excel Programming |