DRIVING ME INSANE same code wont work twice

[lecet444]

Ok I have this code works perfectly, now I want to do that within another sheet. opens this sheet up and do what this code does.

Dim d1 As String
    Dim d2 As String
 
    d1 = TextBox1.Value
    d2 = TextBox2.Value
 
 
 Range("a6:g6").AutoFilter Field:=1, Criteria1:=">=" & d1, Operator:=xlAnd _
   , Criteria2:="<=" & d2
    Dim LR  As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    'For i = 2 To 10000
 
TextBox3.Value = Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value
TextBox4.Value = Range("B" & LR)

This is how I modified it, and it just doesnt work
I get a runtime error "no cells were found"....

Dim dat1 As String
Dim dat2 As String
Dim LR As Long
dat1 = TextBox1.Value
dat2 = TextBox3.Value 
 
 
Workbooks("master sheet.xls").Activate
 Sheets("master sheet").Range("a6:m6").AutoFilter Field:=1, Criteria1:=">=" & dat1, Operator:=xlAnd _
   , Criteria2:="<=" & dat2
 
LR = Range("B" & Rows.Count).End(xlUp).Row
 
    Workbooks("monthly report.xlsm").Sheets("requisition").TextBox4.Value = _
    Workbooks("Master sheet.xls").Sheets("master sheet").Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value
    Workbooks("monthly report.xlsm").Sheets("requisition").TextBox5.Value = _
    Workbooks("master sheet.xls").Sheets("master sheet").Range("B" & LR)

 

[lecet444]

THIS got even more complicated, I made 2 new workbooks, and yourcode worked...I'm gonna try to problem solve it more... thank you for your time

 

[Jonmo1]

Glad to help out...

If the error you get is "No Cells Found" then (and this isn't meant to be sarcastic)...
That error is pretty self explainatory..

It means the SpecialCells(xlCellTypeVisible) Didn't find any cells...there were no visible cells.
Which in turn likely means that the AutoFilter didn't find any cells that met the criteria..

Check the dates in the other book, are they actually dates?

=ISNUMBER(A1)
where A1 is one of those dates

Is it true or false?

 

[lecet444]

OK, in the code I just posted...

What error do you get, on which line??

And where my assumptions correct?

LR = bk2.Range("B" & Rows.Count).End(xlUp).Row

no idea why because it works perfectly with the 2 new workbooks i made (to send to you, if you would have asked) but they work there fine. all the text boxes are active x and so is the button

 

[Jonmo1]

Are both books of the same Excel Version?

If the book holding the Code is xl2007, and the other book is xl2003, then the Rows.Count is the problem..
Because there are ALOT more rows in 2007 then there are in 2003..

Perhaps change that to

LR = bk2.Range("B" & bk2.Cells.Rows.Count).End(xlUp).Row

 

[lecet444]

Glad to help out...

If the error you get is "No Cells Found" then (and this isn't meant to be sarcastic)...
That error is pretty self explainatory..

It means the SpecialCells(xlCellTypeVisible) Didn't find any cells...there were no visible cells.
Which in turn likely means that the AutoFilter didn't find any cells that met the criteria..

Check the dates in the other book, are they actually dates?

=ISNUMBER(A1)
where A1 is one of those dates

Is it true or false?

well i took the same code and put it in the mastersheet workbook(earlier) and it worked. It also worked if i were to change the "B7:B & LR " the 7 to the range that would suffice. so I think the problem was the LR. now with LR = bk2.Range("B" & Rows.Count).End(xlUp).Row it was giving me that error "application defined or user defined error".....it didnt give me that error with the 2 new workbooks that I opened. but Thank You ver much

 

[Jonmo1]

Did you see my last post #14 ?

 

[lecet444]

Did you see my last post #14 ?

OMG you're a GENIUS. yesss that was the problem. thank you very very very much. my day has just turned productive (20 mins left)