excel巨集問題

這是我既設的excel巨集:

Private Sub Label1_Click()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label3_Click()

End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then

If TextBox1.Text <> "" And Application.WorksheetFunction.CountIf(Columns(1), TextBox1.Text) >= 1 Then

TextBox1.Text = ""

KeyCode = 0 '取消vbKeyReturn或vbKeyTab

Else

r = Application.WorksheetFunction.CountA(Columns(1)) + 1

Cells(r, 1) = TextBox1.Text

End If

End If

End Sub

Private Sub TextBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then

r = Application.WorksheetFunction.CountA(Columns(2)) + 1

Cells(r, 2) = TextBox2.Text

End If

End Sub

Private Sub TextBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Or KeyCode = vbKeyReturn Then

r = Application.WorksheetFunction.CountA(Columns(3)) + 1

Cells(r, 3) = TextBox3.Text

End If

End Sub

Private Sub UserForm_Click()

End Sub

我的excel第一列是序號列,0001~9999

我想要在巨集裡加上一格先搜尋的動作,

例如:我要到序號1000那欄,

然後才到那欄位的二、三、四格輸入我的輸入的東西。

請問這程式碼該怎麼寫,既設程式碼應該要修改那裡?

1 個解答

評分
  • 阿戊
    Lv 7
    8 年前
    最佳解答

    Sub test()

    Cells(1, 1).Select

    FS = InputBox("請輸入搜尋值:")

    On Error GoTo err1

    Cells.Find(What:=FS, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _

    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

    ).Activate

    Exit Sub

    err1:

    MsgBox "沒有符合的值!"

    End Sub

還有問題?馬上發問,尋求解答。