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
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 05: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 12:13 AM
vlookup to return the cell positioning where the criteria is found Rock Excel Programming 2 March 12th 05 03:42 PM


All times are GMT +1. The time now is 05:25 PM.

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

About Us

"It's about Microsoft Excel"