Find the correct sheet then find a value on that sheet

[Howard]

This code, in sheet 1 module, searches the other sheets for a date in cell A1. When found then that sheet's A1 is activated by the Application.Goto RngD, True.

What I am having trouble with is, now that I have the correct sheet I want to GoTo a value on that sheet that is listed from C5: to however many rows there may be. (I assume the GoTo A1 is not needed as the code that finds the Column C value will have a GoTo.)

I have tried a Set RngS = .Find(What:=FindStore, _etc. following the
Set RngD = .Find(What:=FindDate, _ but I get an Improper Call error.

So find the sheet that has the Range("D8").Value and once there find the Cell in Column C that has the Range("D4").Value and GoTO it.

Thanks.
Howard


Option Explicit

Sub SearchAOne()
Dim FindDate As String
Dim FindStore As String 'Dimmed but not used yet
Dim RngD As Range
Dim RngS As Range 'Dimmed but not used yet
Dim ws As Worksheet
Dim cnt As Integer
cnt = 0
FindStore = Range("D4").Value 'Dimmed but not used yet
FindDate = Range("D8").Value

If Trim(FindDate) <> "" Then
For Each ws In Worksheets
With ws.Range("A1")
Set RngD = .Find(What:=FindDate, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not RngD Is Nothing Then
Application.Goto RngD, True
cnt = 1
End If
End With
Next ws
If cnt <> 1 Then
MsgBox "Nothing Found!"
End If
End If

End Sub

 

[Claus Busch]

Hi Howard,

Am Mon, 18 Nov 2013 00:47:14 -0800 (PST) schrieb Howard:

This code, in sheet 1 module, searches the other sheets for a date in cell A1. When found then that sheet's A1 is activated by the Application.Goto RngD, True.

What I am having trouble with is, now that I have the correct sheet I want to GoTo a value on that sheet that is listed from C5: to however many rows there may be. (I assume the GoTo A1 is not needed as the code that finds the Column C value will have a GoTo.)

I have tried a Set RngS = .Find(What:=FindStore, _etc. following the
Set RngD = .Find(What:=FindDate, _ but I get an Improper Call error.

So find the sheet that has the Range("D8").Value and once there find the Cell in Column C that has the Range("D4").Value and GoTO it.

try:
Sub SearchAOne()
Dim FindDate As Date
Dim FindStore As String 'Dimmed but not used yet
Dim RngD As Range
Dim RngS As Range 'Dimmed but not used yet
Dim ws As Worksheet
Dim cnt As Integer
Dim LRow As Long
FindStore = Sheets("Sheet1").Range("D4").Value 'Dimmed but not used
yet
FindDate = Sheets("Sheet1").Range("D8")

If FindDate > 0 Then
For Each ws In ThisWorkbook.Worksheets
With ws.Range("A1")
Set RngD = .Find(FindDate, LookIn:=xlValues)
If Not RngD Is Nothing Then
Application.Goto RngD
Exit For
End If
End With
Next ws
End If
If Not RngD Is Nothing Then
With ActiveSheet
LRow = .Cells(.Rows.Count, 3).End(xlUp).Row
Set RngS = .Range("C5:C" & LRow).Find(FindStore, _
LookIn:=xlValues)
If Not RngS Is Nothing Then
RngS.Select
Else
MsgBox "No value for FindStore"
End If
End With
Else
MsgBox "No date found"
End If
End Sub


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Mon, 18 Nov 2013 00:47:14 -0800 (PST) schrieb Howard:

Sub SearchAOne()

Dim FindDate As Date

Dim FindStore As String 'Dimmed but not used yet

Dim RngD As Range

Dim RngS As Range 'Dimmed but not used yet

Dim ws As Worksheet

Dim cnt As Integer

Dim LRow As Long

FindStore = Sheets("Sheet1").Range("D4").Value 'Dimmed but not used

yet

FindDate = Sheets("Sheet1").Range("D8")



If FindDate > 0 Then

For Each ws In ThisWorkbook.Worksheets

With ws.Range("A1")

Set RngD = .Find(FindDate, LookIn:=xlValues)

If Not RngD Is Nothing Then

Application.Goto RngD

Exit For

End If

End With

Next ws

End If

If Not RngD Is Nothing Then

With ActiveSheet

LRow = .Cells(.Rows.Count, 3).End(xlUp).Row

Set RngS = .Range("C5:C" & LRow).Find(FindStore, _

LookIn:=xlValues)

If Not RngS Is Nothing Then

RngS.Select

Else

MsgBox "No value for FindStore"

End If

End With

Else

MsgBox "No date found"

End If

End Sub





Regards

Claus B.

Tried the code and got a Type Mismatch. I suspected the date cell as I was using a string in my earlier tests.

Formatted The A1 cells as date along with the Sheet 1 D8 cell.
Entered a date in Sheet 1 and an identical date in sheet 3 A1.

Tested with this formula and it returned true. (Where A1 is on sheet 3)
=A1=Sheet1!D8

Code returns the message box "No Date found"

So I reformatted Sheet 3, A1 and Sheet 1, D8 to General and entered "FindMe" in each cell.

Changed FindDate to string: Dim FindDate As String 'Date

Changed this If FindDate > 0 Then

To

If FindDate <> "" Then

Code works.

Making it find the date seems to be a problem.

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 18 Nov 2013 02:33:40 -0800 (PST) schrieb Howard:

Making it find the date seems to be a problem.

try:
Dim FindDate As Double

and then:
Set RngD = .Find(CDate(FindDate), LookIn:=xlValues)


Regards
Claus B.

 

[Howard]

Dim FindDate As Double
and then:
Set RngD = .Find(CDate(FindDate), LookIn:=xlValues)

Still getting message box No Date Found.

Here is what I'm running, I may have messed something else up.
Verified the dates match with formula =A1=Sheet1!D8 again and it returns TRUE.

Howard


Sub SearchAOneClaus()
Dim FindDate As Double
Dim FindStore As String
Dim RngD As Range
Dim RngS As Range
Dim ws As Worksheet
Dim cnt As Integer

Dim LRow As Long
FindStore = Sheets("Sheet1").Range("D4").Value
FindDate = Sheets("Sheet1").Range("D8")

If FindDate > 0 Then
For Each ws In ThisWorkbook.Worksheets
With ws.Range("A1")
Set RngD = .Find(CDate(FindDate), LookIn:=xlValues)

If Not RngD Is Nothing Then
Application.Goto RngD

Exit For

End If
End With
Next ws

End If

If Not RngD Is Nothing Then

With ActiveSheet
LRow = .Cells(.Rows.Count, 3).End(xlUp).Row
Set RngS = .Range("C5:C" & LRow).Find(FindStore, _
LookIn:=xlValues)
If Not RngS Is Nothing Then
RngS.Select
Else
MsgBox "No value for FindStore"
End If
End With
Else
MsgBox "No date found"
End If
End Sub

 

[Claus Busch]

Hi Howard,

Am Mon, 18 Nov 2013 03:11:09 -0800 (PST) schrieb Howard:

Still getting message box No Date Found.

Here is what I'm running, I may have messed something else up.
Verified the dates match with formula =A1=Sheet1!D8 again and it returns TRUE.

for me it is working. The code should be in a standard module
=A1=Sheet1!D8 is also true if both values are text.
Please check the format of A1 and Sheet1!D8


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Mon, 18 Nov 2013 03:11:09 -0800 (PST) schrieb Howard:







for me it is working. The code should be in a standard module

=A1=Sheet1!D8 is also true if both values are text.

Please check the format of A1 and Sheet1!D8





Regards

Claus B.

Well, I reformatted the cells for date a couple different times and now it is indeed working. Using the *3/14/2001.

Seemed other formats did not cooperate but I guess it could have been something I was not doing correctly.

I appreciate the help, Claus.

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 18 Nov 2013 08:28:39 -0800 (PST) schrieb Howard:

Well, I reformatted the cells for date a couple different times and now it is indeed working. Using the *3/14/2001.

Excel often makes problems formatting a text formatted date to date.
I format the cells as date and then click in column header =>
TextToColumns => Fixed Width => Finish
Or after formatting as date I write a 1 in an empty cell, copy this
cell, select the cells with date and choose Paste Special => Multiply


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Mon, 18 Nov 2013 08:28:39 -0800 (PST) schrieb Howard:






Excel often makes problems formatting a text formatted date to date.

I format the cells as date and then click in column header =>

TextToColumns => Fixed Width => Finish

Or after formatting as date I write a 1 in an empty cell, copy this

cell, select the cells with date and choose Paste Special => Multiply





Regards

Claus B.

Thanks, I'll stash that little tidbit of info.
Might ease the pain and suffering I seem to have working with dates.

Regards,
Howard