Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whats wrong with this code?
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 -- Joseph ------------------------------------------------------------------------ Joseph's Profile: http://www.excelforum.com/member.php...fo&userid=5637 View this thread: http://www.excelforum.com/showthread...hreadid=320579 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whats wrong with this code?
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. In article , Joseph wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whats wrong with this code?
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 -- Josep ----------------------------------------------------------------------- Joseph's Profile: http://www.excelforum.com/member.php...nfo&userid=563 View this thread: http://www.excelforum.com/showthread.php?threadid=32057 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
whats wrong with this? | Excel Discussion (Misc queries) | |||
Whats wrong with this? | Excel Discussion (Misc queries) | |||
Whats wrong with this line of code? | Excel Programming | |||
whats wrong with this code | Excel Programming | |||
Whats wrong with this code | Excel Programming |