T
Todd Huttenstine
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
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