VBA How to select the inputbox result in a given sheet in a workbook - Object dosen't support

[Palucci]

Hello,

I have a macro that should, after typing in the date input, find this date in the open file data_wb. However, here

For Each ws In data_wb.Sheets("Final")

I have an error " Object dosen't support this property or method"

I would like open file data_wb and on sheet Final going to find my date. Where i did mistake ?

Dim vDate As Date
Dim Loc As Range
Dim data_wb As Workbook

'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.Sheets("Final")
     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

 

[Zot]

Should be just
For Each ws In data_wb.Sheets

and you do not need to activate ws to use it. Can remove
ws.Activate

 

[Palucci]

Thanks, But how to choose data_wb("Final") . Final is a tabs where i would choose data . Because in other tabs I also have date files

 

[Zot]

Thanks, But how to choose data_wb("Final") . Final is a tabs where i would choose data . Because in other tabs I also have date files

Why are you looping all ws if you want to use only sheet Final?

Why not just Set ws=data_wb.Sheets("Final")

If you also need other ws, then put condition like If ws.Name="Final" Then

 

[Palucci]

ok I did, but now this loop does nothing, shows an error, or selects Value which it writes into inputbox; ( Maybe should i change to normal input witout format and Loc .Cells.Find(What:=Format(vDate, "yyyy-mm-dd")) ?

   Set data_wb = Application.Workbooks.Open(file_name)
   Set ws = data_wb.Sheets("Final")
    data_wb.Sheets("Final").Rows("1:1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    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
       
     Set ws = data_wb.Sheets("Final")
    For Each ws In data_wb.Sheets
    
     With ws
        Set Loc = .Cells.Find(What:=Format(vDate, "yyyy-mm-dd"))
        If Not Loc Is Nothing Then
            Loc.Select
            Exit For
        End If
      End With
    Next
    
    End If
End Sub

 

[Zot]

Looks like you get confused with my comment. I don't see the reason to loop all ws in that workbook in this case

Set ws = data_wb.Sheets("Final")
    For Each ws In data_wb.Sheets
   
     With data_wb.Sheets("Final")
        Set Loc = .Cells.Find(What:=Format(vDate, "yyyy-mm-dd"))
        If Not Loc Is Nothing Then
            Loc.Select
            Exit For
        End If
      End With
    Next

Is inputbx a string? Did you get to match the date when trying to Find Loc?

 

[Palucci]

I failed to match the thread, it finds no value. I do not know if my input construction is good, in the file data in the cell is non-standard but it appears as 02/09/2022 e.g. maybe it's the fault of the input construction format

 

[Zot]

I failed to match the thread, it finds no value. I do not know if my input construction is good, in the file data in the cell is non-standard but it appears as 02/09/2022 e.g. maybe it's the fault of the input construction format

Try if this works:
Dim inputbx as String

Change
vDate = DateValue(inputbx)
to
vDate = CDate(inputbx) (this will give date serial to vDate)

This will match the cell formatted as Date in the worksheet.

 

[Palucci]

still nothing happend ... i copied a below all my code after changes:

Dim vDate As Date
Dim Loc As Range
Dim data_wb As Workbook
Dim ws As Worksheet
Dim inputbx As String

'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)
   'paste copy like value and change to date format'
   data_wb.Sheets("Final").Rows("1:1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "YYYY-MM-DD"
    'set our ws'
   Set ws = data_wb.Sheets("Final")
   
   
''' Put date input box  '''
    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 = CDate(inputbx)
        On Error GoTo 0
        DateIsValid = IsDate(vDate)
        If Not DateIsValid Then MsgBox "Please enter a valid date.", vbExclamation
    Loop Until DateIsValid
''' loop'''
     With data_wb.Sheets("Final")
        Set Loc = .Cells.Find(what:=Format(vDate, "YYYY-MM-DD"))
        If Not Loc Is Nothing Then
            Loc.Select
        End If
      End With
    End If
End Sub

 

[Palucci]

Uff its resolved Zot ! i changed here

Set Loc = .Cells.Find(what:=Format(inputbx, "YYYY-MM-DD"))