Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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.

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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

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

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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?

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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.

Activate the cell which was found by vlookup formula
 

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

Activate the cell which was found by vlookup formula
 

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