Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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?
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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?



  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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.
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula have a VLOOKUP but want it to =0 if no value is found Nicole Excel Worksheet Functions 3 October 26th 09 06:20 AM
How to return the address of a cell found via VLOOKUP? artisdepartis Excel Programming 3 July 6th 07 10:54 AM
How to get the address of the cell found our by vlookup dilettante Excel Programming 3 August 25th 06 01:41 AM
vlookup to return cell position as to where result was found Rock Excel Programming 7 March 13th 05 01:13 AM
vlookup to return the cell positioning where the criteria is found Rock Excel Programming 2 March 12th 05 04:42 PM


All times are GMT +1. The time now is 10:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"