Using VBA to create a named cell with defined variable

Question

I am trying to name a cell that will be referenced in a formulas using name manager via VBA. I have VBA locate the correct cell on the sheet (the location of which will vary) and define it as a variable:

s3form = Range(prog_beg3).Offset(0, 3)

I then name the cell using the name manager functionality so that the cell has the address of 's3form' but with the row being variable. So if 's3form = B5', the address of the named cell would be '$B5'.

ActiveWorkbook.Names.Add Name:=s3_form, RefersTo:=s3form

However, the formula then doesn't recognize s3form as a cell. It seems to only recognize the cell when 's3form = $B$5'

Does anyone have a possible solution?

Thanks

Use Set: Set s3form = Range(prog_beg3).Offset(0, 3) Of course s3form must be declared as a range. Then RefersTo:=s3form.Address — Scott Craner
@ScottCraner When I try ActiveWorkbook.Names.Add Name:=sform, RefersTo:=s3form.Address I get Run-time error '424' Object required — A.Nelson
Please show more of your code. How are your variables declared and assigned values? — Scott Craner
Range(prog_end2).Offset(4, 0).Select prog_beg3 = Selection.Address(RowAbsolute:=False, ColumnAbsolute:=False) s3form = Sheets("testmode").Range(prog_beg3).Offset(0, 3).Address(RowAbsolute:=False, ColumnAbsolute:=True) ActiveWorkbook.Names.Add Name:=sform, RefersTo:=s3form.Address — A.Nelson

Vityata Vityata · Accepted Answer · 2017-09-28T19:45:53

This is how to do it:

activeworkbook.Names.Add "Testing",[s3form]

Now "Testing" would be added to the names in the workbook, refering to s3form. Concerning your code, it should probably work if you use the [] around s3form.

In general, these [] are a nice way to refer to named ranges. You may refer even to addresses like this - [A1].

Using VBA to create a named cell with defined variable

1 Answers