vb - ActiveWorkbook.Names.Add

Question

I have created a simple macro that contains a While  / Wend statement.  Within this loop I'm attempting to create a new name for the active cell.  this name will decrease by 1 for 12 times.  The problem I'm having is with the "R402C3" variable.  I created this macro using the record macro function.

My question is how do I change the "R402C3" to represent the next cell.

The new row is between 28 & 30 rows less than the current row depending on how many days in the month.

How do I change the R402C3 to R368C3 and then 32-34 rows lower for the next cell?

 After setting the name to a cell I move the cursor up twice.  This process happens 12 times so it needs to create a new name for 12 different cells.

The names are:

  high_80_12

  high_80_11

  ......

  high_80_01

The worksheet is called Phx_80

Here's the macro:

Sub Name_set()

'

' Name_set Macro

'

' Keyboard Shortcut: Ctrl+Shift+X

   Dim c_name As String

   Dim yyyy, mm As Integer

   yyyy = InputBox("Enter New Year (yy) ", "  NEW  YEAR ")

   mm = 12

   c_loop = 12

   While mm >= 1

    c_name = "high_" & yyyy & "_" & mm

    ActiveWorkbook.Names.Add Name:=c_name, RefersToR1C1:= _

        "=Phx_1980!R402C3"

    Selection.End(xlUp).Select

    Selection.End(xlUp).Select

    mm = mm - 1

   Wend

End Sub

thanks for your time.

El Bee

Answer 1

After much frustration I finally found what I was looking for.  IT is:  Range(cell location).Name.  it's the ".Name" I needed.

So below is the macro I've created to do what I needed.  It runs about 20 seconds because there's 480 cells that need to have names assigned.  That's 480 names per decade.

If you look at this and have suggestions for improving it I'm willing to give it a try.  If you find this macro usefully please let me know.

Sub Set_Cell_Names()

 ' this macro will add cell names to a range of cells; 4 cells per month

 ' and 10 years.  So, 480 cell names will be created.

 ' Be patient, the run process time is 15-20 seconds per worksheet

 ' It only creates cell names for the current worksheet that is opened.

    Dim AvgHighStr, AvgLowStr, city_yr, MaxHighStr, MinLowStr, Month, tab_name, YearStr As String

    Dim AvgHigh, AvgLow, Mth_cntr, Year_cntr, Row_nbr As Integer

    Maxhigh = "Max_high_"

    MinLow = "Min_low_"

    AvgHigh = "Avg_high_"

    AvgLow = "Avg_low_"

    tab_name = ActiveSheet.Name   ' extract the name for the worksheet into a variable name

 'extract 4 digits in tab year

 'tab names arer:  Tab 1900's, Tab 1910's thru Tab 2010's

    city_yr = Mid(tab_name, (InStr(1, tab_name, "'") - 4), 4)

    Year_cntr = 0

    Row_nbr = 35

    While Year_cntr <= 9  ' loops 10 times: 0-9

        Mth_cntr = 1

        Month = Right("0" & Mth_cntr, 2)

        YearStr = Right("0" & Year_cntr, 2)

        While Mth_cntr < 13  ' loops 12 times to populate cells for each year

          ' creating naming strin values for Max, Min, & Averages

            MaxHighStr = Maxhigh & city_yr & "_" & YearStr & "_" & Month

            MinLowStr = MinLow & city_yr & "_" & YearStr & "_" & Month

            AvgHighStr = AvgHigh & city_yr & "_" & YearStr & "_" & Month

            AvgLowStr = AvgLow & city_yr & "_" & YearStr & "_" & Month

          ' calculating cell locations and added names to corresponding cells

            cell_loc = "C" & Row_nbr

            Range(cell_loc).Name = MaxHighStr

            cell_loc = "E" & Row_nbr

            Range(cell_loc).Name = MinLowStr

            Row_nbr = Row_nbr + 1

            cell_loc = "C" & Row_nbr

            Range(cell_loc).Name = AvgHighStr

            cell_loc = "E" & Row_nbr

            Range(cell_loc).Name = AvgLowStr

          ' okay, 1 month of cells have been named now add 1 to year and redo

          ' this will set up cell names for month in each year for the work sheet

          ' that is showing.

            Row_nbr = Row_nbr + 34

            Mth_cntr = Mth_cntr + 1

            Month = Right("0" & Mth_cntr, 2)

        Wend

          ' okay, 1 year of cells have been named now add 1 to year and redo

          ' this will set up cell names for the years 00 thru 09 for the decade

          ' that is showing.

            Year_cntr = Year_cntr + 1

    Wend

End Sub

Answer 2

Thanks for the idea Jim,

I tried that but the syntax for ActiveCell.Address = "$column$row ($3$401"  and that won't work in the RefersTo: value

This is how the function needs to look like:  (used record macro to get this)

     ActiveWorkbook.Names.Add Name:="high_80_12", RefersToR1C1:="=Phx_1980!R402C3"

I'm able to create the Name parm and the RefersTo parm but I can't concatenate the function correctly.

What I don't understand is how to build the "ActiveWorkbook.Name:= function and include the 2 variables.

here's what I've attempted:

     ActiveWorkbook.Names.Add Name:=c_name, RefersToR1C1:=c_row

     c_name = "high_80_12"

      c_row = "=PHX_1980!R401C3"

Answer 3

Re:  where am i in the loop

In your original post you are selecting a cell during each loop.

So replace the line...

ActiveWorkbook.Names.Add Name:=c_name, RefersToR1C1:="=Phx_1980!R402C3"

With...

ActiveWorkbook.Names.Add Name:=c_name, RefersTo:="=Phx_1980!" & ActiveCell.Address

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2  (Dropbox)

(free & commercial excel programs)

Answer 4

since I'm not sure what the next row number will be I set another variable to represent that number; but now I get the, "Run-time error '5':  Invalid procedure call or argument"  ? on this command:

        ActiveWorkbook.Names.Add Name:=c_name, RefersTo:=Cells(c_row)

c_name = "high_80_12"

c_row    =  "Phx_1980!402C3"

==================================

Sub Name_set()

'

' Name_set Macro

'

' Keyboard Shortcut: Ctrl+Shift+X

   Dim c_name, c_row As String

   Dim row_nbr, yyyy, mm As Integer

   yyyy = InputBox("Enter New Year (yy) ", "  NEW  YEAR ")

   mm = 12

   c_loop = 12

   row_nbr = ActiveCell.Row

   c_row = "Phx_1980!" & row_nbr & "C3"   'creating cell location

   MsgBox c_row

   c_name = "high_" & yyyy & "_" & mm       'creating name for current cell

   ActiveWorkbook.Names.Add Name:=c_name, RefersTo:=Cells(c_row)

'moving the cursor up twice to next cell with formula

   Selection.End(xlUp).Select

   Selection.End(xlUp).Select

   c_row = "Phx_1980" & "!row_nbr" & "C3"

   While mm >= 1

    c_name = "high_" & yyyy & "_" & mm

    ActiveWorkbook.Names.Add Name:=c_name, RefersTo:=Cells(c_row)

    Selection.End(xlUp).Select

    Selection.End(xlUp).Select

    row_nbr = ActiveCell.Row

    c_row = "Phx_1980" & "!row_nbr" & "C3"

    mm = mm - 1

   Wend

End Sub

Answer 5

Hi,

You can try something like:

Sub Test()

   Dim r As Integer

   r = 402

   ActiveWorkbook.Names.Add Name:="Hello", RefersTo:=Cells(r, "A")

End Sub

Change the value of r to address the new row.