not finding "foundRng"

[gtd526]

Hello,

My Private Sub is not finding "foundRng", which is todays date and it does exist in column A (7/16/2021).
Thank you.

Here is the code:

Private Sub Workbook_Open()
'using todays date as foundRng

Dim foundRng As Range

    Sheets("BJs Income").Select
    Set foundRng = Range("A:A").Find(Date)
   
    If Not (foundRng Is Nothing) Then
          foundRng.Select
          ActiveCell.Offset(0, 11).Select
          Selection.Copy
          ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
              :=False, Transpose:=False
      Else
      End If
      foundRng.Select
   
End Sub

 

[Joe4]

Are you sure it is entered in column A as valid date, and not a text entry?
Does it have a time component that may be being suppressed by the format of the cell?

By the way, you should place your "Select" command that you currently have at the end of your code in your IF block, as you do not want to try to select it if you cannot find it (that will cause an error), i.e.

Private Sub Workbook_Open()
'using todays date as foundRng

Dim foundRng As Range

    Sheets("BJs Income").Select
    Set foundRng = Range("A:A").Find(Date)
   
    If Not (foundRng Is Nothing) Then
          foundRng.Select
          ActiveCell.Offset(0, 11).Select
          Selection.Copy
          ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
              :=False, Transpose:=False
          foundRng.Select
      Else
          MsgBox "Cannot find current date in column A"
      End If
       
End Sub

 

[gtd526]

Are you sure it is entered in column A as valid date, and not a text entry?
Does it have a time component that may be being suppressed by the format of the cell?

By the way, you should place your "Select" command that you currently have at the end of your code in your IF block, as you do not want to try to select it if you cannot find it (that will cause an error), i.e.

Private Sub Workbook_Open()
'using todays date as foundRng

Dim foundRng As Range

    Sheets("BJs Income").Select
    Set foundRng = Range("A:A").Find(Date)
  
    If Not (foundRng Is Nothing) Then
          foundRng.Select
          ActiveCell.Offset(0, 11).Select
          Selection.Copy
          ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
              :=False, Transpose:=False
          foundRng.Select
      Else
          MsgBox "Cannot find current date in column A"
      End If
      
End Sub

Column A:A is formatted as a Date, and it is a formula (A37=A36+7).
Here is the data:

Income - Schedule.xlsm
	A	B	C				G	H	I	J	K	L	M	N
33	6/25/2021	$0.00					$0.00	$0.00	$0.00	$0.00	$0.00		6/13	6/19
34							Diff:	-$1,405.00	Rent/Bills=	$1,405.00	$0.00
35	7/2/2021	$345.10	29.37				$0.00	$21.40	$5.01	$26.41	$318.69		6/20	6/26
36	7/9/2021	$316.31	26.92				$0.00	$19.61	$4.59	$24.20	$292.11		6/27	7/3
37	7/16/2021	$0.00					$0.00	$0.00	$0.00	$0.00	$0.00		7/4	7/10
38	7/23/2021	$0.00					$0.00	$0.00	$0.00	$0.00	$0.00		7/11	7/17
39	7/30/2021	$0.00					$0.00	$0.00	$0.00	$0.00	$0.00		7/18	7/24
40							Diff:	-$794.20	Rent/Bills=	$1,405.00	$610.80
BJs Income

Cell Formulas
Range	Formula
A33,A36:A39	A33	=A32+7
B33,B37:B39,B35	B33	=C33*11.75
G33,G37:G39,G35	G33	=IF($B33>1000,$B33*0.013822435,"$0.00")
I33,I35:I39	I33	=B33*0.014505962
J33,J35:J39	J33	=SUM(G33:I33)
M33,M35:M39	M33	=A33-12
N33,N35:N39	N33	=M33+6
A35	A35	=A33+7
H33,H35:H39	H33	=B33*0.062011088
H34,H40	H34	=K34-J34
K33,K35:K39	K33	=B33-J33
K34	K34	=SUM(K30:K33)
K40	K40	=SUM(K35:K39)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H8,H13,H18,H24,H29,H34,H40,H45,H50,H56,H61,H66	Cell Value	>0	text	NO
H8,H13,H18,H24,H29,H34,H40,H45,H50,H56,H61,H66	Cell Value	<0	text	NO

 

[Joe4]

Try temporarily changing the format of column A to include a time component in your date.
Then what does the data look like?

 

[dmt32]

Hi,
try changing this line

Set foundRng = Range("A:A").Find(Date)

to this

Set foundRng = Range("A:A").Find(CStr(Date), LookIn:=xlValues, lookat:=xlWhole)

and see if this resolves your issue

Dave

 

[Alex Blakenburg]

Dates are pretty nasty in terms of the find command.

Try this (I started with Joe's version)

Private Sub Workbook_Open()
'using todays date as foundRng

    Dim foundRng As Range
    Dim findDate As Date                ' XXX Added
    
    Sheets("BJs Income").Select
    findDate = CLng(Date)               ' XXX Added
    Set foundRng = Range("A:A").Find(what:=findDate, LookIn:=xlFormulas) ' XXX Modified
   
    If Not (foundRng Is Nothing) Then
          foundRng.Select
          ActiveCell.Offset(0, 11).Select
          Selection.Copy
          ActiveCell.PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
              :=False, Transpose:=False
          foundRng.Select
      Else
          MsgBox "Cannot find current date in column A"
      End If
       
End Sub

 

[gtd526]

Hi,
try changing this line

Set foundRng = Range("A:A").Find(Date)

to this

Set foundRng = Range("A:A").Find(CStr(Date), LookIn:=xlValues, lookat:=xlWhole)

and see if this resolves your issue

Dave

Yes. This resolves the issue.
Thank you.

 

[dmt32]

Yes. This resolves the issue.
Thank you.

you are welcome glad we were able to help

Appreciate the feedback

Dave