Whats wrong with this code?

[Joseph]

If TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox2.Value = "" And
TextBox3.Value = "" Then
Label4.Caption = "Please check your date entries and try again."
Exit Sub
End If

sheets("sheet3").Select
Range("A1").Select
Application.ScreenUpdating = False
Dim date1, date2, firsttwodigitsofdate1, firsttwodigitsofdate2,
usersname, mid2digitsofdate1, mid2digitsofdate2,
howevermanyworkbooksareopen, lasttwodigitsofdate1,
lasttwodigitsofdate2, 'filenamewillbe
date1 = TextBox2.Value
date2 = TextBox3.Value

firsttwodigitsofdate1 = Left(date1, 2)
firsttwodigitsofdate2 = Left(date2, 2)

mid2digitsofdate1 = Mid(date1, 3, 2)
mid2digitsofdate2 = Mid(date2, 3, 2)

lasttwodigitsofdate1 = Mid(date1, 5, 2)
lasttwodigitsofdate2 = Mid(date2, 5, 2)

While Len(ActiveCell.Value) > 0 And firsttwodigitsofdate1 <=
firsttwodigitsofdate2
firsttwodigitsofdate1 = firsttwodigitsofdate1 + 0
firsttwodigitsofdate2 = firsttwodigitsofdate2 + 0

usersname = ActiveCell.Value 'this is a range of about 140 usernames in
a column

On Error Resume Next 'for when the workbook doesn't exist

Workbooks.Open ("P:\clevedon staff\activities\" & usersname & "\" &
usersname & " " & firsttwodigitsofdate1 & mid2digitsofdate1 &
midtwodigitsofdate2 & lasttwodigitsofdate1 & " activitylog.csv")

Workbooks(1).Activate
ActiveCell.Offset(1, 0).Select
If firsttwodigitsofdate1 > firsttwodigitsofdate2 Then
Exit Sub
End If

If ActiveCell.Value = Empty Then
Range("A1").Select
firsttwodigitsofdate1 = firsttwodigitsofdate1 + 1
End If

Wend
'Next i
sheets("reports").Select
Label4.Caption = "Reports for the range " & TextBox2.Value & " to " &
TextBox3.Value & " generated on " & Now & "."
CommandButton9.Enabled = False
Call populate

End Sub

Sub populate()

Application.ScreenUpdating = True
Workbooks(1).Activate
sheets("reports").Select
Dim MyCell As Range
Dim Wb As Workbook
Dim MyFormula As String
Dim currcell
Dim mystr

Set MyCell = ThisWorkbook.sheets("reports").Range("B2")

For Each Wb In Workbooks

If Wb.Name <> ThisWorkbook.Name Then

MyFormula = MyFormula & "'" & Wb.Name & "'" & "!R2C2" & ","

End If

Next Wb

MyCell.FormulaR1C1 = "=SUM(" & Left(MyFormula, Len(MyFormula) - 1) &
")"

Call finds

End Sub

Sub finds()

Application.ScreenUpdating = True
Workbooks(1).Activate
sheets("reports").Select

Range("B2").Select

Cells.Find(What:="$", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
Activate

Cells.Replace What:="$", Replacement:="", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False

Selection.AutoFill Destination:=Range("B2:B25"),
Type:=xlFillDefault

Range("B2:B25").Select

Selection.AutoFill Destination:=Range("B2:H25"),
Type:=xlFillDefault
sheets("reports").Range("A30").Select

ActiveCell.Value = UserForm1.TextBox2.Value & " " & "to" & " " &
UserForm1.TextBox3.Value



Call closetherest

End Sub

Sub closetherest()
'UserForm1.Hide
On Error Resume Next
sheets("reports").Select
Dim Wb As Workbook
Dim AWb As String
AWb = ActiveWorkbook.Name

For Each Wb In Workbooks
If Wb.Name <> AWb Then
Wb.Close
End If
Next Wb
Application.ScreenUpdating = True
Range("B2:H25").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
sheets("reports").Range("A28").Value = "Date Range: " & TextBox2.Value
& " " & TextBox3.Value


End Sub

THe prob I get is that if I open a date range of more than ten days it
doesn't bring it in! All the workbooks will open, but the formula
doesn't rack up. But if theres only about 5 files to open it seems to
work fine.

Any ideas?
Cheers

 

[JE McGimpsey]

While someone may have the time to try to decipher your long block of
code, I suspect most people won't, even if they manage to wade through
the code to see your problem description at the bottom of the post.

Please do some more troubleshooting first, and include only the relevant
code (and if what you posted is only the relevant code, you probably
need to break your question up into parts).

JFYI: I have no idea what you mean by "it doesn't bring it in", or "the
formula doesn't rack up". I'm sure you understand it very well, but
others haven't been working with your code before. At least describe
what the code is *supposed* to do, what it is or isn't doing, and what
you've done to try to isolate the problem.

 

[Joseph]

Whoa, easy there JE McGimpsey. Im quite new to this scene, and while
appreciate your comments are polite they are also quite pointed. S
please, take it easy on a newcomer. It would have been alot easier t
say "Can you give me more of an idea of whats wrong please with a mayb
a few more detailed pointers on the problem you're having?", rather tha
launching into a whole diatribe about the ins and outs of how to conduc
yourself in a troubleshooting forum. So, that said, after reading you
rather humiliating and almost insulting reply, here are some mor
pointers on the trouble I am having. I don't expect you to help an
wouldn't particularly care if you didn't as you obviously would rathe
pick a newcomer to pieces than help out, so for the benefit of anyon
else, apologies for the original vagaries, and here is a more detaile
look at the problem I have.

I have a userform in the workbook, with two textboxes and a comman
button. Textbox1 contains the starting date range, while textbox
contains the ending date range. As you can see there is some strin
manipulation there, but this is just to make sure the users ar
inputting correct date ranges (this all works fine). I have a list o
usernames in one sheet down a column. I have basically said that whil
the length of the activecell is greater than zero, keep looking fo
files containing a file extension of *.csv, a date value between th
values of textbox1 and textbox2 as well as the username in the activ
cell. Hence, the name of the file is constructed like so:

activecell.value & " " & textbox1.value & " " & "activitylog.csv"

The code loops through the list of usernames constantly using th
offset command until the variable storing date1 is equal to th
variable storing date2. The files do all open without any problem
however it seems certain date ranges won't work. A formula is the
created using this piece of code:

sheets("reports").Select
Dim MyCell As Range
Dim Wb As Workbook
Dim MyFormula As String
Dim mystr

Set MyCell = ThisWorkbook.sheets("reports").Range("B2")

For Each Wb In Workbooks

If Wb.Name <> ThisWorkbook.Name Then

MyFormula = MyFormula & "'" & Wb.Name & "'" & "!R2C2" & ","

End If

Next Wb

MyCell.FormulaR1C1 = "=SUM(" & Left(MyFormula, Len(MyFormula) - 1)
")"


However this code doesn't always work. The cell it is supposed to en
up in is B2, but when I look at B2 it is empty. Could this be becaus
of the size of the formula Im generating? The sub titled "finds()" i
geared to find all instances of the $ symbol, but obviously if th
formula isn't there it won't find anything. It then proceeds t
autofill the range I ask it to, but there is no formula to autofil
with.

Regard