Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
On Monday, November 18, 2013 1:28:35 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Mon, 18 Nov 2013 00:47:14 -0800 (PST) schrieb Howard: 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. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
On Monday, November 18, 2013 4:44:25 AM UTC-8, Claus Busch wrote:
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. 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find the correct sheet then find a value on that sheet
On Monday, November 18, 2013 10:50:19 AM UTC-8, Claus Busch wrote:
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. Thanks, I'll stash that little tidbit of info. Might ease the pain and suffering I seem to have working with dates. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value from one sheet and replace value in second sheet | Excel Programming | |||
macro to find data from one sheet & copy in another sheet | Excel Programming | |||
Find value in sheet 1 and copy matching row from sheet 2 | Excel Programming | |||
Find value from sheet 1 on sheet 2 and copy to an offset from there | Excel Programming | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming |