我写了一个语法,其中包括大小写选择和一个msgbox。我希望语法找到电子表格中的第一个活动单元格,并根据一些预先确定的问题和答案将其移动到 A1 或 A2,但命令无法以最佳方式工作。有人能帮我吗?
我希望消息框根据先前问题的答案直观地弹出,但是消息框最终陷入循环似乎存在事故。
我已经在下面附加了代码。
Public Sub SurvAnalysis()
Dim InpSh As Worksheet
Dim fCell As Range
Dim msg1 As String, msg2 As String
Dim Ans1 As Variant, Ans2 As Variant
Set InpSh = ThisWorkbook.Worksheets("Input")
msg1 = "Are these headers included in the Data, and is the data in the correct format? { Dob ∏ StartDate ∏ End Date }"
Ans1 = MsgBox(msg1, vbYesNoCancel, " Data type")
Select Case Ans1
Case vbYes
On Error Resume Next
Set fCell = InpSh.Cells.Find(What:="*", _
After:=InpSh.Cells(Rows.Count, Columns.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
On Error GoTo 0
If fCell Is Nothing Then
MsgBox "All cells are blank."
Else
fCell.CurrentRegion.Cut Destination:=InpSh.Range("A1")
End If
GoTo Quit:
Case vbCancel
MsgBox ("Get your data sorted out")
GoTo Quit:
Case vbNo
GoTo Option2:
End Select
Quit:
Option2:
msg2 = "Are the data in the correct manner and do you wish for us to include the headers on your behalf?"
Ans = MsgBox(msg2, vbYesNo, "Sort Data")
Select Case Ans
Case vbYes
Set fCell = InpSh.Cells.Find(What:="*", _
After:=InpSh.Cells(Rows.Count, Columns.Count), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If fCell Is Nothing Then
MsgBox "All cells are blank."
Else
fCell.CurrentRegion.Cut Destination:=InpSh.Range("A2")
InpSh.Range("A1").Value = " Dob"
InpSh.Range("B1").Value = " StartDate"
InpSh.Range("C1").Value = " End Date"
End If
Case vbNo
MsgBox ("Get your data sorted out")
GoTo Quit:
End Select
End Sub
你的 goto 结束语句Quit:
应该在最后。在你的代码中,当它进入Quit:
它将继续并处理它下面的所有代码行。 也只是作为一般的经验法则,这可能是意见,但通常您始终可以正确编写代码,而无需使用任何goto
语句。 它们造成的问题比它的价值还要多。 看看你是否可以在没有任何goto
语句的情况下编写你的逻辑,你就会被设定为终身。