Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Various Random Codes not working from time to time
Hey guys
This is a very strange problem. My work runs Windows 2000 Professional with Excel 2000. I thought this was a glitch with my computer at first, but now I am starting to think there is more of a problem than that. One day I was running a report (using WORKING code that I have already written). When I say working I mean it has been working correctly for about 10 other times. Then this one day I go to run my report with my code and it just didnt work. I can not remember the exact code and variable names but I know from trying to debug it that it was not creating the appropriate value for the variable or not even creating the value at all. The variable was nothing. Also my code was exiting early before any exit sub, end sub or goto procedure. During debugging it, it just exited the code and gave NO error. It hought it may have had something to do with the variables not being created or assigned the correct values so I tried to rename the variables and that did not work. I then restarted my computer and had the same problem. So for some reason I decided to restart again and all of a sudden, it magically started working. Well I thought that was a problem with my PC so I forgot about. That was until yesterday. I wasted 3 hours trying to figure out why my code was not working. Below is my code: Private Sub CommandButton3_Click() If MsgBox("Confirm date. Continue?", vbYesNo) = vbNo Then Exit Sub End If Worksheets("Reps Current").Select Worksheets("Reps Current").ComboBox1.ListIndex = WeeklyReportDatesFollowupsSups.ComboBox1.ListIndex Worksheets("Sups Data").Select Unload Me Worksheets("Reps Current").Select Worksheets("Reps Current").ComboBox2.Value = "View Detail by Supervisor" Worksheets("Sups Data").Select Dim FindRange As Variant Dim x As Integer Dim ret_value Dim col_index As Integer Dim row_index As Integer Dim ColSelection1 Dim ColSelection2 Dim ColSelection3 Dim ColSelection1A Dim ColSelection2B Dim ColSelection3C Dim Rng1 As Range Dim Cell1 As Object Dim Counter As Long Dim NumCountForDel Dim Location On Error Resume Next FindRange = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Sups Data").Range("B1:GW1").Find (ComboBox1.Value) row_index = 1 col_index = 2 For col_index = 2 To 204 If Cells(row_index, col_index).Value = FindRange Then row_index = 1 Cells(row_index, col_index).Select End If Next col_index 'To return Column Index Number use below code ColSelection1A = ActiveCell.Column - 2 ColSelection2B = ActiveCell.Column - 1 ColSelection3C = ActiveCell.Column 'To return Column Letter use below code ColSelection1 = Left(ActiveCell.Offset(0, -1).Address(, False), Application.WorksheetFunction.Find("$", ActiveCell.Address(, False)) - 1) ColSelection2 = Left(ActiveCell.Offset(0, 0).Address(, False), Application.WorksheetFunction.Find("$", ActiveCell.Address(, False)) - 1) ColSelection3 = Left(ActiveCell.Offset(0, 1).Address(, False), Application.WorksheetFunction.Find("$", ActiveCell.Address(, False)) - 1) NumCountForDel = Application.WorksheetFunction.CountA (Sheets("Sups Data").Range("A:A")) Counter = 2 'NEW CODE************** Dim RngA As Range Dim RngB As Range Dim CellA As Object Dim CellB As Object Dim FindRangeA Dim FindRangeB Dim FindRangeAAddress Dim FindRangeBAddress Set RngA = Worksheets("Sups Data").Range("A3:3000") Set RngB = Worksheets("Reps Current").Range("A7:A3000") For Each Cell In RngB FindRangeA = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Sups Data").Range("A3:A3000").Find(Cell) FindRangeB = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Reps Current").Range("A7:A3000").Find (FindRangeA) Location = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Reps Current").Range("A7:A3000").Find (FindRangeA).Address If FindRangeB = "" Then Else FindRangeAAddress = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Sups Data").Range("A3:A3000").Find (Cell).Address Sheets("Sups Data").Range(FindRangeAAddress).Offset (0, ColSelection1A).Value = Sheets("Reps Current").Range (Location).Offset(1, 2).Value Sheets("Sups Data").Range(FindRangeAAddress).Offset (0, ColSelection2B).Value = Format(Sheets("Reps Current").Range(Location).Offset(0, 2).Value, "0.00%") Sheets("Sups Data").Range(FindRangeAAddress).Offset (0, ColSelection3C).Value = Sheets("Reps Current").Range (Location).Offset(2, 2).Value End If Next 'END NEW CODE************** 'Sheets("Sups Data").Range(ColSelection1 & NumCountForDel + 2 & ":" & ColSelection3 & NumCountForDel + 3).Select 'Selection.ClearContents Worksheets("Reps Current").Select Worksheets("Reps Current").ComboBox2.Value = "View Detail by Employee" Worksheets("Sups Data").Select MsgBox ("Report successfully created for " & FindRange & "!") The problem was the same problem as experienced before. This time it was only the variable problem, not the early exiting. Everything works fine up until the lines: Set RngA = Worksheets("Sups Data").Range("A3:3000") Set RngB = Worksheets("Reps Current").Range("A7:A3000") For Each Cell In RngB FindRangeA = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Sups Data").Range("A3:A3000").Find(Cell) FindRangeB = Workbooks("Completed Followup Detail CSS.XLS").Sheets("Reps Current").Range("A7:A3000").Find (FindRangeA) First problem was that For each Cell in RngB statement. Cell was equal to nothing as well as FindRangeA and FindRangeB. Now I clearly defined the 2 ranges in the previous 2 lines so the variables should have been SOMETHING, not nothing. When I changed the line from: For Each Cell In RngB to For Each Cell In RngA, the variables starting working again but obviously that would not work because I need to use RngB, not A. Now I run the code once while the line is: For Each Cell In RngA It it runs fine the first time, and then I go to run it again it the same problem happens. I restart 2 times and still same problem. I go to another computer and still same problem. I go home and come in today. I try to run the code again today and it WORKS. I ran it 5 different times today and each time it work perfectly. I wasted 3 hours yesterday with this. Im sorry for this long post but can anyone please tell me what the problem could be? Thank you Todd Huttenstine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time delay refresh on random number | Excel Worksheet Functions | |||
Total of two time keeping codes in one cell | Excel Discussion (Misc queries) | |||
Entering a random date and time in a cell | Excel Discussion (Misc queries) | |||
Excel Charts - Time line for random points on X axis | Excel Worksheet Functions | |||
How do I sort area codes by time zones | Excel Discussion (Misc queries) |