How to quickly name cells in a row using VBA?

[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

 

[Fayyaadh Ebrahim]

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

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

 

[Dave Peterson]

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

 

[Ken Johnson]

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

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

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

 

[Fayyaadh Ebrahim]

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