VBA . 用于设置范围-Java 学习之路

我是Excel的新手，但有一点似乎超级强大的是使用.Find来命名范围 . 不幸的是，我只看到人们在事后使用消息框和类似的东西 . 我想做的事情有点复杂 .

我试图在'rec'工作表中找到一个名为'State'的单元格 . 在找到它之后，我需要在“状态”单元格上方的一个单元格中设置一个范围，直到六个单元格 .

这是我到目前为止：

Dim rec As Worksheet

Set rec = ThisWorkbook.Sheets("Rec")

Dim State As String

Dim MoveHeadersUp As Range

State = "State"


Set MoveHeadersUp = rec.Rows.Find(What:=State, LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If MoveHeadersUp Is Nothing Then

MsgBox "Uh-Oh. Column A is missing a header titled 'State'. You better create one!"

Else

???????

End Sub

2 回答

Else

'''THIS IS THE ANSWER
Set spaceabove = Range(MoveHeadersUp.Offset(IIf(MoveHeadersUp.Row > 1, -1, 0), 0).Address, MoveHeadersUp.Offset(IIf(MoveHeadersUp.Row > 6, -6, (MoveHeadersUp.Row - 1) * -1), 0).Address)

Debug.Print spaceabove.Address

End If
'''I was trying to delete the rows 
spaceabove.EntireRow.Delete

End Sub

回复于 2024-05-05T19:56:30+08:00

Dim SpaceAbove as Range 

If MoveHeadersUp.Row>6 Then
    Set SpaceAbove = MoveHeadersUp.Offset(-6,0).Resize(6,1)
    Debug.print SpaceAbove.Address
Else
    msgbox "Can't move up 6 rows from 'MoveHeadersUp' !"
End If

回复于 2024-05-05T19:56:30+08:00

VBA . 用于设置范围

2 回答

相关问题