Find a Date accross multiple sheets 2

[robcarr]

Dear All,

I have a date in a1, on one workbook, i need to find the date in a1 in another workbook, i can do everything apart from use the date in A1 in the find and search all worksheets, the find will be looking through 6 sheets and I need to locate the date in that sheet. The date only appears once in 1 sheet.

Any help greatly appreciated.

Rob.

 

[kylua]

Sub tester()
Dim wkbook As Workbook
Dim wksheet As Worksheet
'obtain the date. Run macro when sheet with source date is active or adjust code to find correct sheet.
Set sourcedate = ActiveSheet.Cells(1, 1)
For Each wkbook In Workbooks'loop thru the open workbooks
If wkbook.Name <> ActiveWorkbook.Name Then 'eliminate active workbook
For Each wksheet In wkbook.Worksheets'loop thru the sheets
Set checkdate = wksheet.Cells(1, 1)'set to &quot;A1&quot;
If checkdate.Value = sourcedate.Value Then
MsgBox (&quot;The date is on sheet &quot; + wksheet.Name + _
&quot; in workbook &quot; + wkbook.Name)
End If
Next wksheet
End If
Next wkbook
End Sub

No doubt there is an easier way of doing it, but this works.

 

[robcarr]

I think I understand how the coding is working, thanks for doing that, the date that is being searched for could be in any cell on the opened workbook, in the range a1 to I3, I have tried to adjust the coding but couldn't get it to work.

Any ideas greatly appreciated.

rob.

 

[kylua]

Replace:
Set sourcedate = ActiveSheet.Cells(1, 1)

with:
Set sourcedate = ActiveCell

And it will search for the value in the active cell. If you're running this from a command button, make sure the command button doesn't take focus on click. I would also advise that you add:
if isdate(sourcedate.value) then
do rest of code

otherwise you may find yourself searching for blank spaces if you click by accident.

 

[robcarr]

I am not having much joy with this, I am getting a little confused I think, all I want to do is find a date in a workbook, the date can be anywhere on any sheet in the workbook, the date I want to find is stored in A1 on my main workbook, I am using the coding so far, but I get the &quot;object variable or with block variable not set&quot; error.

Current Coding

Sub update_Root_calls()

Sheets(&quot;Main&quot.Select
pathname = [c6].value
sourcedate = ActiveSheet.Cells(1, 4)
Workbooks.Open Filename:=pathname
Sheets(&quot;sheet4&quot.Select
Range(&quot;e5:e15&quot.Select
Selection.Copy
ActiveWindow.ActivateNext

Sheets(&quot;main&quot.Select
pathname2 = [c16].value
fn2 = [c21].value
Workbooks.Open Filename:=pathname2
Sheets(Array(&quot;Week 1&quot;, &quot;week 2&quot;, &quot;week 3&quot;, &quot;Week 4&quot;, &quot;Week 5&quot).Select
Sheets(&quot;Week 1&quot.Activate
Cells.Find(What:=sourcedate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

thanks

 

[kylua]

Sorry, misunderstood the question.
Assuming
Cells.Find(What:=sourcedate, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

Works OK, (you didn't say where the error occured) then replacing:
Set checkdate = wksheet.Cells(1, 1)'set to &quot;A1&quot;
If checkdate.Value = sourcedate.Value Then
MsgBox (&quot;The date is on sheet &quot; + wksheet.Name + _
&quot; in workbook &quot; + wkbook.Name)

In the eg I gave should work. Now I have done a find across multiple sheets before but the code's at work. And I have to go and set up my 86 yr old Gran on the internet so I may be gone some time.
I'll pick up the code from work tomorrow and post it if you haven't found a solution before then.

 

[robcarr]

the error I mentioned earlier relates to the find, it doesn't like the sourcedate part of the find, I would like to try and use the find method above if possible to locate the value that is stored in sourcedate (sourcedate holds the date value that is stored in D1).

Thanks for you help so far on this.

 

[kylua]

Sorry about the delay, senile dementia is my only excuse, but here is the code that I developed for searching a full workbook:

Sub findall()
'
'Macro created 11/01/2002 by Colin Robinson
'

On Error GoTo errortrap
response = InputBox(&quot;Please enter the word that you wish to search for.&quot;,
&quot;Search&quot
For n = 1 To Worksheets.Count
Worksheets.Activate
Set myrange = Worksheets.Range(&quot;A1:AZ20&quot
myrange.Cells.Find(What:=response, After:=Range(&quot;A1&quot,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Do
response2 = MsgBox(&quot;If you wish to continue to search this sheet, click
'Yes'&quot; _
+ Chr(13) + &quot;If you wish to move to the next sheet, click 'No'&quot; _
+ Chr(13) + &quot;If you wish to cancel press, well, I'll let you guess that
one.&quot;, vbYesNoCancel, &quot;Move on&quot
If response2 = vbCancel Then Exit Sub
If response2 = vbYes Then Cells.FindNext(After:=ActiveCell).Activate
Loop Until response2 = vbNo
backhere:
Next n
GoTo finale
errortrap:
MsgBox (&quot;Nothing on this Sheet, Click OK to go to the next.&quot
Resume backhere
finale:

End Sub


This provides the basics.
Add:
Application.screenupdating = false/true
Auto move to next sheet.
Loop thru the workbooks as well. I don't know if they're open or whether you're opening them to order.
Return activecell.address with the workbook and sheet name or number.
Get response from the cell that you want to search from. The reason it was bugging at that point in your code is cos you need sourcecell.value so you are only want to get the contents and not the entire object, (I think).

And that should do it. (it was a while ago I did this so no laughing)

PS I was referring to me when I said senile dementia, not my 86 yr old Gran who is getting to grips with her new computer nicely. The card games anyway, just cos she needs to practise with the mouse of course.

 

[Nitibob]

Hi Kylua,

I have been working on something like this - and since being relatively new to VBA was bashing my head against the preverbial brickwall

Your code has given me several ideas

Thanks

nitibob

 

[robcarr]

thanks for doing this for me Kylua, I realised that the coding worked fine, but the dates in the spreadsheet needed to be formatted a different way, your first suggestion wortks a treat and has resolved the issue perfectly thanks.

rob.