需要获取单元格值并将其自动声明到行范围

Question

名称管理器使用vba... 我需要从列中逐个获取单元格值，并且必须将该名称声明为该列旁边的行范围

例

在第D列中，我有姓名列表

我必须获取该 D1 值并将该值声明到行范围( E1:S1 )

下一个

必须D2 ---> E2:S2

Answer 1

这是前 5 行应该这样做的方式：

For i = 1 To 5
ThisWorkbook.Names.Add Name:=yourWorksheet.Cells(i, 4).Value, RefersTo:=yourWorksheet.Range(yourWorksheet.Cells(i, 5), yourWorksheet.Cells(i, 19))
Next

请记住，名称必须是唯一的

Answer 2

尝试以下代码

Sub AddNamedRange()
Dim cel As Range
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet4")  'change Sheet4 to your sheet name
With ws
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row    'last row with data in Column D
For Each cel In .Range("D1:D" & lastRow)     'loop through all cell in Column D
ThisWorkbook.Names.Add cel, ws.Range(cel.Offset(, 1), cel.Offset(, 15)) 'adding named range
Next
End With
End Sub