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

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

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.

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

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

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

On Thursday, 6 May 2021 at 18:34:22 UTC+2, Claus Busch wrote:
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

Good morning Claus,
yes, that looks much better! I didn't know the end of month function. Very good, though.
There is no code in your file, isn't it? I couldn't find any.
I'm still having the problem that the code is not selecting the cell in range H7:H18 which has the same date
as cell E3. Am I missing something?

Regards,
Norbert

Set c = .Range("H7:H18").Find(myDate, after:=Range("H18"))

What roll does [after:=Range("H18")] play?
Why H18?

Hi Norbert,

Am Thu, 6 May 2021 22:11:35 -0700 (PDT) schrieb Norbert:

Set c = .Range("H7:H18").Find(myDate, after:=Range("H18"))

What roll does [after:=Range("H18")] play?
Why H18?

in OneDrive are macros disabled.
You must download the file to test it.

Without the line above Excel searches after H7 and a match in H7 will
not be found.


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Hi Claus,
I've downloaded a copy of your file,
assigned the macro to a button,
pressed the button,
nothing is happening.

I'm using Excel 2019. Is there something different to your version of 2016?

Hi Norbert,

Am Fri, 7 May 2021 00:22:10 -0700 (PDT) schrieb Norbert:

I've downloaded a copy of your file,
assigned the macro to a button,
pressed the button,
nothing is happening.

I tested it with several dates and it always worked as expected.
I don't know why it doesn't work on your machine.
Can you send me the workbook?


Regards
Claus B.
--
Windows10
Microsoft 365 for business

On Friday, 7 May 2021 at 09:38:37 UTC+2, Claus Busch wrote:
I tested it with several dates and it always worked as expected.
I don't know why it doesn't work on your machine.
Can you send me the workbook?
Regards
Claus B.
--
Windows10
Microsoft 365 for business

Strange!
After renaming the macro (just because I didn't like the name I gave before) and re-assigning it
to the button, it works.
But the downloaded copy of your file, still doesn't work. Maybe you can have a look at it and see why?
I'll send them both to you.

Hi Norbert,

Am Fri, 7 May 2021 02:24:18 -0700 (PDT) schrieb Norbert:

Strange!
After renaming the macro (just because I didn't like the name I gave before) and re-assigning it
to the button, it works.
But the downloaded copy of your file, still doesn't work. Maybe you can have a look at it and see why?
I'll send them both to you.

have another look at OneDrive and download the new version.


Regards
Claus B.
--
Windows10
Microsoft 365 for business

On Friday, 7 May 2021 at 11:27:14 UTC+2, Claus Busch wrote:

have another look at OneDrive and download the new version.
Regards
Claus B.
--
Windows10
Microsoft 365 for business

Hi Claus,
now it works! Thank you very much!
I include the code here for others. I'm sure you don't mind.

Sub Test()
Dim myRow As Long
Dim myDate As Double

With ActiveSheet
myDate = .Range("E3")
If Application.CountIf(.Range("H7:H18"), myDate) 0 Then
myRow = Application.Match(myDate, .Range("H1:H18"), 0)
Application.Goto .Range("H" & myRow)
End If
End With
End Sub

Regards,
Norbert