Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi,
I had a similar problem before and I found the code from that time but I still can't figure it out. I have a date in cell E3 (e.g. "01-05-2021") which also can be found in range H7:H18. That range has all the dates of the 1st day of each month. I enter the date into cell E3 and then I run a macro. I want the macro to automatically select the particular cell within the range H7:H18, which has the same date as cell E3. This is the part of the macro I'm struggling with: Dim c As Range Dim myStr As String Range("E3").Select Selection.Copy With ActiveSheet myStr = .Range("E3") End With With ActiveSheets Set c = .Range("H7:H18").Find(myStr) If Not c Is Nothing Then Application.Goto .Range("H" & c.Row) End With (I'm getting this error message: "Object required".) I'm hoping that someone can help me. Thanks! Norbert |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Norbert,
Am Thu, 6 May 2021 05:36:29 -0700 (PDT) schrieb Norbert: I had a similar problem before and I found the code from that time but I still can't figure it out. I have a date in cell E3 (e.g. "01-05-2021") which also can be found in range H7:H18. That range has all the dates of the 1st day of each month. I enter the date into cell E3 and then I run a macro. I want the macro to automatically select the particular cell within the range H7:H18, which has the same date as cell E3. try: Dim c As Range Dim myDate As Date With ActiveSheet myDate = .Range("E3") Set c = .Range("H7:H18").Find(myDate, after:=Range("H18")) If Not c Is Nothing Then Application.Goto c End With Regards Claus B. -- Windows10 Microsoft 365 for business |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Thursday, 6 May 2021 at 15:42:12 UTC+2, Claus Busch wrote:
Hi Norbert, Am Thu, 6 May 2021 05:36:29 -0700 (PDT) schrieb Norbert: I had a similar problem before and I found the code from that time but I still can't figure it out. I have a date in cell E3 (e.g. "01-05-2021") which also can be found in range H7:H18. That range has all the dates of the 1st day of each month. I enter the date into cell E3 and then I run a macro. I want the macro to automatically select the particular cell within the range H7:H18, which has the same date as cell E3. try: Dim c As Range Dim myDate As Date With ActiveSheet myDate = .Range("E3") Set c = .Range("H7:H18").Find(myDate, after:=Range("H18")) If Not c Is Nothing Then Application.Goto c End With Regards Claus B. -- Windows10 Microsoft 365 for business Hi Claus, good to hear from you. Unfortunately, nothing is happening. The active cell is not changing. |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Norbert,
Am Thu, 6 May 2021 07:02:14 -0700 (PDT) schrieb Norbert: Unfortunately, nothing is happening. The active cell is not changing. check if numberformats are different in E3 and H7:H18 Regards Claus B. -- Windows10 Microsoft 365 for business |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Thursday, 6 May 2021 at 16:06:50 UTC+2, Claus Busch wrote:
Hi Norbert, Am Thu, 6 May 2021 07:02:14 -0700 (PDT) schrieb Norbert: Unfortunately, nothing is happening. The active cell is not changing. check if numberformats are different in E3 and H7:H18 Regards Claus B. -- Windows10 Microsoft 365 for business Hi Claus, E3 and H7:H18 are formatted as Date (mm-dd-yyyy) but this is the content of H7:H18 H7: =Settings!B5 it shows: 01-03-2021 H8: =H7+31 01-04-2021 H9: =H8+28 01-05-2021 H10: =H9+31 01-06-2021 H11: =H10+30 01-07-2021 H12: =H11+31 01-08-2021 H13: =H12+31 01-09-2021 H14: =H13+30 01-10-2021 H15: =H14+31 01-11-2021 H16: =H15+30 01-12-2021 H17: =H16+31 01-01-2022 H18: =H17+30 01-02-2022 On my settings sheet cell B5, I have the starting date of the tax year, which is always the 01.March The formulas where I add the number of days is obviously not very clever but I didn't know better. Regards, Norbert |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi Norbert,
Am Thu, 6 May 2021 07:21:37 -0700 (PDT) schrieb Norbert: Hi Claus, E3 and H7:H18 are formatted as Date (mm-dd-yyyy) but this is the content of H7:H18 H7: =Settings!B5 it shows: 01-03-2021 H8: =H7+31 01-04-2021 H9: =H8+28 01-05-2021 H10: =H9+31 01-06-2021 H11: =H10+30 01-07-2021 H12: =H11+31 01-08-2021 H13: =H12+31 01-09-2021 H14: =H13+30 01-10-2021 H15: =H14+31 01-11-2021 H16: =H15+30 01-12-2021 H17: =H16+31 01-01-2022 H18: =H17+30 01-02-2022 On my settings sheet cell B5, I have the starting date of the tax year, which is always the 01.March The formulas where I add the number of days is obviously not very clever but I didn't know better. have a look: https://1drv.ms/x/s!AqMiGBK2qniTgfBE...5Tugg?e=w0FUsg Regards Claus B. -- Windows10 Microsoft 365 for business |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula have a VLOOKUP but want it to =0 if no value is found | Excel Worksheet Functions | |||
How to return the address of a cell found via VLOOKUP? | Excel Programming | |||
How to get the address of the cell found our by vlookup | Excel Programming | |||
vlookup to return cell position as to where result was found | Excel Programming | |||
vlookup to return the cell positioning where the criteria is found | Excel Programming |