Search 2 sheets for a date

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
Hi

The below code searches Sheet 1 ('Training Log') for a date in Column 1.

VBA Code:
Sub FindDate()
Dim myDt As Date
Dim myInput As Variant
Dim CellFound As String

If ActiveSheet.Name <> "Training Log" And ActiveSheet.Name <> "Training 1981-1997" Then
    MsgBox "The Locate Entry Date function will only run in Training Log", vbInformation, "Function Invalid in This Sheet"
Exit Sub
    Else
End If

    On Error GoTo Error1
myInput = InputBox("Enter Date Below: (d/m/yy)", "Locate Training Log Entry Date")
    If myInput = "" Then
        MsgBox "Search cancelled!", vbInformation, "Locate Training Log Entry Date"
        Exit Sub
    Else
 
    End If
 
    If IsDate(myInput) Then
        myDt = myInput
        CellFound = Application.Match(CDbl(myDt), ActiveSheet.Range("A:A"), 0)
        ActiveSheet.Range("A" & CellFound).Activate
        Exit Sub
 
    Else
 
    End If
 
Error1:
MsgBox "Sorry, no Training Log entry found for that date", vbInformation, "Search Unsuccessful"

End Sub

I'm looking for a small amendment to this so the code will also search the same column in Sheet 20 ('Training 1981-1997').

The code is also contained in Sheet 1 and Sheet 20 under
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Can the amended code search Sheet 20 in Sheet 1 and vice versa, or can it only search the sheet that the code is in? If it won't do this, I presume it would need to be inserted in This Workbook?

Many thanks!
 
Last edited:

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
If the active cell in Sheet 1 is in A1-A11 do not run the code
If the active cell in Sheet 20 is in A1 do not run the code
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Question, should the doubleclick only run the code if the active cell is in column A excluding the cells mentioned above? If the active cell is in column C should it run?
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
The code should only run when double clicking Sheet 1 Cells A12 onwards and Sheet 20 Cells A2 onwards, no other columns.
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Place this code in the Sheet 1 vbe
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row > 11 Then
        Call FindDate
    End If
End Sub

Place this code in the Sheet 20 vbe
VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 And Target.Row > 1 Then
        Call FindDate
    End If
End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
871
Office Version
  1. 365
Platform
  1. Windows
Perfect!

Thank you so much!
 

Forum statistics

Threads
1,148,390
Messages
5,746,432
Members
424,016
Latest member
Shizzl

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