Selecting a cell according to the cell value
Hello All VB expert
Need help with a Macro. I would like to be able to select a cell according to a variable in a cell. For example: Cell A1 31/04/2010. Cell A3 = 31/05/2010 Cell A4 = 30/06/2010 Cell A5 = 31/07/2010 Cell A6 = 31/04/2010 In this case when I run the Marco, it will select Cell A6 because it is the same value to Cell A1. And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the same value to Cell A1 If you need more information or my explanation is not clear please let me know. Thank you very much for your help. Harn |
Selecting a cell according to the cell value
See this page
http://www.ozgrid.com/VBA/find-dates.htm Sub FindDate() Dim strdate As String strdate = Format(Range("A1"), "Short Date") On Error Resume Next Set rcell = Range("A2:A10000").Find _ (What:=CDate(strdate), After:=Range("A2"), _ LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False) On Error GoTo 0 If rcell Is Nothing Then MsgBox "Date cannot be found" Else Application.Goto rcell End If End Sub -- Regards Dave Hawley www.ozgrid.com "Harn88" wrote in message ... Hello All VB expert Need help with a Macro. I would like to be able to select a cell according to a variable in a cell. For example: Cell A1 31/04/2010. Cell A3 = 31/05/2010 Cell A4 = 30/06/2010 Cell A5 = 31/07/2010 Cell A6 = 31/04/2010 In this case when I run the Marco, it will select Cell A6 because it is the same value to Cell A1. And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the same value to Cell A1 If you need more information or my explanation is not clear please let me know. Thank you very much for your help. Harn |
Selecting a cell according to the cell value
Try the below
Cells.Find(What:=Range("A1").Value, After:=Range("A1"), _ LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext).Activate PS: Try recording a macro and edit to suit... -- Jacob (MVP - Excel) "Harn88" wrote: Hello All VB expert Need help with a Macro. I would like to be able to select a cell according to a variable in a cell. For example: Cell A1 31/04/2010. Cell A3 = 31/05/2010 Cell A4 = 30/06/2010 Cell A5 = 31/07/2010 Cell A6 = 31/04/2010 In this case when I run the Marco, it will select Cell A6 because it is the same value to Cell A1. And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the same value to Cell A1 If you need more information or my explanation is not clear please let me know. Thank you very much for your help. Harn |
All times are GMT +1. The time now is 02:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com