MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 304|Reply: 3

Match using today's date or next closest date [SOLVED]

[Copy link]

9

Topics

11

Posts

43

Integral

Member

Rank: 2

Integral
43
Post on 2-12-2017 01:27:05 | All posts |Read mode
I am having trouble getting the following to work. It always goes to the 'else' portion of the sub. What I am trying to achieve is to set a print range based on finding today's date.The range has all days of the week except Sunday. So if today is Sunday then ideally it would return the following Monday. But for now I would be happy if I could at least get it to return the row for today. Do I have to apply a format to 'Date' or something for it to work?
Sub printPrep()
Dim rowNum As Long
Dim rng As Range
Set rng = Sheet2.Range(A:A)
If Not IsError(Application.Match(Date, rng)) Then
    rowNum = Application.Match(Date, rng)
    MsgBox rowNum
Else
    MsgBox error
End If
End Sub




Reply

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-12-2017 01:35:01 | All posts




Sub
printPrep()
   
Dim
rowNum
As

Variant
   
Dim
rng
As
Range
   
Dim
d
As

Date
   
   
Set
rng = Sheet2.Range(A:A)
    d =
Date
   
If
Weekday(d) = vbSunday
Then
d = d + 1
   
    rowNum = Application.Match(CLng(d), rng, 0)
   
   
If

Not
IsError(rowNum)
Then
        MsgBox rowNum
   
Else
        MsgBox error
   
End

If
End
Sub












Reply Support Opposition

Props Report

9

Topics

11

Posts

43

Integral

Member

Rank: 2

Integral
43
 Author| Post on 2-12-2017 02:17:41 | All posts



Thanks it! I never thought of converting to an integer. Thanks much!




Reply Support Opposition

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-12-2017 03:44:53 | All posts



You're welcome.
Yeah, dates in Excel always seem to be a little quirky.
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-21-2017 14:35 , Processed in 0.169926 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!