• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Given Date-14 Get data ? Stuck

yamihero777

New Member
Hello Excel gurus,

Trying to create an Serious Userform which could reduce 2hr work to 20 mins. This is part of my project which i'm very much stuck

Objective : Get data from a certain column if given date (userform) between given date -14 days

1. of Course there is unique value if got matched then given date then get data
2. Only unique value but multiple dates different sells values
3. 14 days is constant . between 14 days if unique value got sale again fetch date value .
4. i'm one of there employee, not a professional excel guru's . Help me my colleagues from deadly working hours. :(

Got this from one of the forms. but no luck. i'm getting type mismatch error sub_date = date_ref - 14

>>> use code - tags <<<

Code:
Sub LastRemark()
Dim date_ref, sub_date As Date
Dim datesheet As Worksheet

Set datesheet = Worksheets("AllTaskDump")

date_ref = Trim(MIAform.dateBox.Text)
sub_date = date_ref - 14     

date_ref = format(date_ref, "dd-mm-yyyy")
sub_date = format(sub_date, "dd-mm-yyyy")

With datesheet
   
     If .FilterMode Then .ShowAllData
    
     .Range("AD:AD").AutoFilter Field:=1, Criteria1:=">=" & sub_date, Operator:=xlAnd, Criteria2:="<=" & date_ref
    
     MIAform.lastBox.Text = .Range("Q:Q").SpecialCells(xlCellTypeVisible).Value
    
     MIAform.rptBox.Text = "yes"
    
End With
  
End Sub
Please reply for any more information .
 
Last edited by a moderator:

vletm

Excel Ninja
yamihero777
Code:
date_ref = Trim(MIAform.dateBox.Text)
sub_date = date_ref - 14
The code seems to use date as text
... and after that the code tries to -14 from text
... that won't work well ... or how?
Could You try to modify code that like date_ref = MIAform.dateBox.value?
... then date_ref should be number (date)
... and from number value ... would be more useful to continue...
As well as ...
if the code has challenges with AutoFilter
You could use date_ref = "<=" & clng(date_ref) & sub_date = ">=" & clng(sub_date)
 

yamihero777

New Member
yamihero777
Code:
date_ref = Trim(MIAform.dateBox.Text)
sub_date = date_ref - 14
The code seems to use date as text
... and after that the code tries to -14 from text
... that won't work well ... or how?
Could You try to modify code that like date_ref = MIAform.dateBox.value?
... then date_ref should be number (date)
... and from number value ... would be more useful to continue...
As well as ...
if the code has challenges with AutoFilter
You could use date_ref = "<=" & clng(date_ref) & sub_date = ">=" & clng(sub_date)
Thank you @veltm... your points told what i was doing
Thank you so much . I figured it out.

Note to others : please don't use auto filters they don't work most of the time.. at least for me

Good day
 
Top