How to quickly name cells in a row using VBA?

  • Thread starter Thread starter Fayyaadh Ebrahim
  • Start date Start date
F

Fayyaadh Ebrahim

Hi

I have to name 75 cells in a row starting from cell B4. B4 will be
named dbtr1, C4 dbtr 2 and so on.

I tried using the following code but it doesn't actually put the name
of the cell in the name box (top left had corner), however it does
show up when I go to Insert -> Name -> Define and it refers to the
correct cell.

I'd like the name to show up in the name box when I click the little
drop down that shows you all the named ranged in the workbook.

Sub Macro1()

Dim i As Integer

For i = 1 To 75

'ActiveWorkbook.Names.Add Name:="dbtr" & i, RefersToR1C1:="=Output
Database!R4C" & i + 1
ActiveWorkbook.Names("dbtr" & i).Delete

Next i

End Sub
 
Oops, ignore this part (it was commented out from previous code I had
there anyway):

ActiveWorkbook.Names("dbtr" & i).Delete
 
Option Explicit

Option Explicit
Sub testme01()
Dim i As Long
With Worksheets("Output database")
For i = 1 To 75
.Cells(4, "A").Offset(0, i).Name = "dbtr" & i
Next i
End With
End Sub
 
Oops, ignore this part (it was commented out from previous code I had
there anyway):

ActiveWorkbook.Names("dbtr" & i).Delete
Click to expand...

Hi,

I used...

Sub Macro1()

Dim i As Integer

For i = 1 To 75

ActiveWorkbook.Names.Add Name:="dbtr" & i, _
RefersToR1C1:="=OutputDatabase!R4C" & i + 1


Next i

End Sub

in a workbook with a sheet named OutputDatabase and all 75 names
appeared in the Name Box drop down list.

Ken Johnson
 
Hi

Thanks very much, that worked perfectly.

Option Explicit

Option Explicit
Sub testme01()
Dim i As Long
With Worksheets("Output database")
For i = 1 To 75
.Cells(4, "A").Offset(0, i).Name = "dbtr" & i
Next i
End With
End Sub
Click to expand...
 
Back
Top