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

Palucci

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a macro that should, after typing in the date input, find this date in the open file data_wb. However, here
Rich (BB code):
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 ?


Rich (BB code):
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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")) ?


Rich (BB code):
   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

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
Looks like you get confused with my comment. I don't see the reason to loop all ws in that workbook in this case
Rich (BB code):
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

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
2,006
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

Palucci

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
still nothing happend ... :( i copied a below all my code after changes:
Rich (BB code):
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

Banned user
Joined
Sep 15, 2021
Messages
122
Office Version
  1. 365
Platform
  1. Windows
Uff its resolved Zot ! i changed here
Rich (BB code):
Set Loc = .Cells.Find(what:=Format(inputbx, "YYYY-MM-DD"))
:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,480
Messages
5,770,330
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top