时间戳

首页 > 解决方案 > VBA 选择输入框中指示的项目

excel - VBA 选择输入框中指示的项目

问题描述

我想在输入框中输入一个日期后，我希望它选择一个日期或date_wb中的一个条目。但是，我的宏不仅没有错误地完成它


'Open file
file_name = Application.GetOpenFilename(Title:="Choose a target Workbook")
If file_name <> False Then
   'Set data file
   Set data_wb = Application.Workbooks.Open(file_name)
   
   'Input box

   Do
       inputbx = InputBox("Enter Date, FORMAT; YYY-MM-DD", , Format(VBA.Now, "YYYY-MM-DD"))
       If inputbx = vbNullString Then Exit Sub
       inputstr = Split(inputbx, "-")
       On Error Resume Next
       InputDate = DateSerial(inputstr(2), inputstr(0), inputstr(1))
       On Error GoTo 0
       DateIsValid = IsDate(InputDate)
       If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
   Loop Until DateIsValid

标签： excelvbaexcel-formula

解决方案

我认为选择部分缺少你。如果您想在另一本书中查找并选择某个日期，您可以执行以下操作：

Dim vDate As Date
Dim Loc As Range

'Open file
file_name = Application.GetOpenFilename(Title:="Choose a target Workbook")
If file_name <> False Then
   'Set data file
   Set data_wb = Application.Workbooks.Open(file_name)
       
    Do
        inputbx = InputBox("Enter Date, FORMAT; YYYY-MM-DD", , format(VBA.Now, "YYYY-MM-DD"))
        If inputbx = vbNullString Then Exit Sub
        On Error Resume Next
        vDate = DateValue(inputbx)
        On Error GoTo 0
        DateIsValid = IsDate(vDate)
        If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
    Loop Until DateIsValid
       
     
    For Each ws In data_wb.Worksheets
     ws.Activate
     With ws
        Set Loc = .Cells.Find(What:=vDate) 'It will find the firs cell that cointains the date as a date like (09/23/2021)
        If Not Loc Is Nothing Then
            Loc.Select
            Exit For
        End If
        
        Set Loc = .Cells.Find(What:=format(vDate, "yyyy-mm-dd")) 'It will find the date stored as a text like ('2021-09-23)
        If Not Loc Is Nothing Then
            Loc.Select
            Exit For
        End If
      End With
    Next

只需确定日期的存储方式，因为它会作为价值找到。

更新了代码。

这将查找日期并从找到的列中复制从第 109 行到第 123 行的列范围。

Public Sub Main()

Dim vDate As Date
Dim Loc As Range

'Open file
file_name = Application.GetOpenFilename(Title:="Choose a target Workbook")
If file_name <> False Then
   'Set data file
   Set data_wb = Application.Workbooks.Open(file_name)
       
    Do
        inputbx = InputBox("Enter Date, FORMAT; YYYY-MM-DD", , format(VBA.Now, "YYYY-MM-DD"))
        If inputbx = vbNullString Then Exit Sub
        On Error Resume Next
        vDate = DateValue(inputbx)
        On Error GoTo 0
        DateIsValid = IsDate(vDate)
        If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
    Loop Until DateIsValid
       
     'The previous For each means will look for into all book sheet by sheet
     
     'If you want to look for into an sprecific sheet you have to do on this way
     data_wb.Worksheets("Final").Activate
     With data_wb.Worksheets("Final")
        Set Loc = .Cells.Find(What:=vDate) 'It will find the firs cell that cointains the date as a date like (09/23/2021)
        If Not Loc Is Nothing Then
            'This part just copy the range of 109 to 123 to the found column
            vArr = Split(Cells(1, Loc.Column).Address(True, False), "$")
            colLoc = vArr(0)
            data_wb.Worksheets("Final").Range(colLoc & "109:" & colLoc & "123").Copy
        End If
        
        Set Loc = .Cells.Find(What:=format(vDate, "yyyy-mm-dd")) 'It will find the date stored as a text like ('2021-09-23)
        If Not Loc Is Nothing Then
            'This code just select the range found
            Loc.Select
        End If
    End With
    
    
End If
End Sub

推荐阅读