Macro hangs my excel (not responding)

see anything wrong with this, it cause excel 2002 to "not respond" when the
button is clicked

Private Sub Button2_Click()

Dim IIndex As Integer
Dim JIndex As Integer
Dim Tests As Integer
Dim Fill As Integer
Dim Ctr As Integer
Dim IIxx As Integer
Dim JIxx As Integer
Dim CbeRange As Range
Dim Myrow As Range
Dim Mycol As Range
Dim MC As Integer
Dim MR As Integer
Dim CB As Integer

Ctr = 0
For IIndex = 7 To 15
For JIndex = 2 To 10

If Ctr <> 1 And IsNull(Cells(IIndex, JIndex)) Then


If 2 <= JIndex And JIndex <= 4 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("B7:D9")
If 5 <= JIndex And JIndex <= 7 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("E7:G9")
If 8 <= JIndex And JIndex <= 10 And 7 <= IIndex And IIndex <= 9 Then
CbeRange = Range("H7:J9")
If 2 <= JIndex And JIndex <= 4 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("B10:D12")
If 5 <= JIndex And JIndex <= 7 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("E10:G12")
If 8 <= JIndex And JIndex <= 10 And 10 <= IIndex And IIndex <= 12 Then
CbeRange = Range("H10:J12")
If 2 <= JIndex And JIndex <= 4 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("B13:D15")
If 5 <= JIndex And JIndex <= 7 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("E13:G15")
If 8 <= JIndex And JIndex <= 10 And 13 <= IIndex And IIndex <= 15 Then
CbeRange = Range("H13:J15")

If 7 = IIndex Then Myrow = Range("B7:J7")
If 8 = IIndex Then Myrow = Range("B8:J8")
If 9 = IIndex Then Myrow = Range("B9:J9")
If 10 = IIndex Then Myrow = Range("B10:J10")
If 11 = IIndex Then Myrow = Range("B11:J11")
If 12 = IIndex Then Myrow = Range("B12:J12")
If 13 = IIndex Then Myrow = Range("B13:J13")
If 14 = IIndex Then Myrow = Range("B14:J14")
If 15 = IIndex Then Myrow = Range("B15:J15")

If 2 = JIndex Then Mycol = Range("B7:B15")
If 3 = JIndex Then Mycol = Range("C7:C15")
If 4 = JIndex Then Mycol = Range("D7:D15")
If 5 = JIndex Then Mycol = Range("E7:E15")
If 6 = JIndex Then Mycol = Range("F7:F15")
If 7 = JIndex Then Mycol = Range("G7:G15")
If 8 = JIndex Then Mycol = Range("H7:H15")
If 9 = JIndex Then Mycol = Range("I7:I15")
If 10 = JIndex Then Mycol = Range("J7:J15")

Ctr = 0
For Tests = 1 To 9
MC = 0
If Range(Mycol).Find(Tests, LookIn:=xlValues) Then MC = 1
MR = 0
If Range(Myrow).Find(Tests, LookIn:=xlValues) Then MR = 1
CB = 0
If Range(CbeRange).Find(Tests, LookIn:=xlValues) Then CB = 1
If MC = 0 And MR = 0 And CB = 0 Then
Ctr = Ctr + 1
Fill = Tests
JIxx = JIndex
IIxx = IIndex
End If
Next Tests

If Ctr = 1 Then
Cells(IIxx, JIxx) = Fill
Cells(IIxx, JIxx).Font.Bold = True
Cells(IIxx, JIxx).Font.Color = 5
End If

End If

Next JIndex
Next IIndex
Loop

End Sub


--
Jeff

Have you tried tracing through the code using the F8 key to see where you are
getting hung up? The other question is do you have on change events that may
be firing based on these changes? That could be hanging things up...
--
HTH...

Jim Thomlinson


"Jeff" wrote:

> see anything wrong with this, it cause excel 2002 to "not respond" when the
> button is clicked
>
> Private Sub Button2_Click()
>
> Dim IIndex As Integer
> Dim JIndex As Integer
> Dim Tests As Integer
> Dim Fill As Integer
> Dim Ctr As Integer
> Dim IIxx As Integer
> Dim JIxx As Integer
> Dim CbeRange As Range
> Dim Myrow As Range
> Dim Mycol As Range
> Dim MC As Integer
> Dim MR As Integer
> Dim CB As Integer
>
> Ctr = 0
> For IIndex = 7 To 15
> For JIndex = 2 To 10
>
> If Ctr <> 1 And IsNull(Cells(IIndex, JIndex)) Then
>
>
> If 2 <= JIndex And JIndex <= 4 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("B7:D9")
> If 5 <= JIndex And JIndex <= 7 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("E7:G9")
> If 8 <= JIndex And JIndex <= 10 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("H7:J9")
> If 2 <= JIndex And JIndex <= 4 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("B10:D12")
> If 5 <= JIndex And JIndex <= 7 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("E10:G12")
> If 8 <= JIndex And JIndex <= 10 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("H10:J12")
> If 2 <= JIndex And JIndex <= 4 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("B13:D15")
> If 5 <= JIndex And JIndex <= 7 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("E13:G15")
> If 8 <= JIndex And JIndex <= 10 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("H13:J15")
>
> If 7 = IIndex Then Myrow = Range("B7:J7")
> If 8 = IIndex Then Myrow = Range("B8:J8")
> If 9 = IIndex Then Myrow = Range("B9:J9")
> If 10 = IIndex Then Myrow = Range("B10:J10")
> If 11 = IIndex Then Myrow = Range("B11:J11")
> If 12 = IIndex Then Myrow = Range("B12:J12")
> If 13 = IIndex Then Myrow = Range("B13:J13")
> If 14 = IIndex Then Myrow = Range("B14:J14")
> If 15 = IIndex Then Myrow = Range("B15:J15")
>
> If 2 = JIndex Then Mycol = Range("B7:B15")
> If 3 = JIndex Then Mycol = Range("C7:C15")
> If 4 = JIndex Then Mycol = Range("D7:D15")
> If 5 = JIndex Then Mycol = Range("E7:E15")
> If 6 = JIndex Then Mycol = Range("F7:F15")
> If 7 = JIndex Then Mycol = Range("G7:G15")
> If 8 = JIndex Then Mycol = Range("H7:H15")
> If 9 = JIndex Then Mycol = Range("I7:I15")
> If 10 = JIndex Then Mycol = Range("J7:J15")
>
> Ctr = 0
> For Tests = 1 To 9
> MC = 0
> If Range(Mycol).Find(Tests, LookIn:=xlValues) Then MC = 1
> MR = 0
> If Range(Myrow).Find(Tests, LookIn:=xlValues) Then MR = 1
> CB = 0
> If Range(CbeRange).Find(Tests, LookIn:=xlValues) Then CB = 1
> If MC = 0 And MR = 0 And CB = 0 Then
> Ctr = Ctr + 1
> Fill = Tests
> JIxx = JIndex
> IIxx = IIndex
> End If
> Next Tests
>
> If Ctr = 1 Then
> Cells(IIxx, JIxx) = Fill
> Cells(IIxx, JIxx).Font.Bold = True
> Cells(IIxx, JIxx).Font.Color = 5
> End If
>
> End If
>
> Next JIndex
> Next IIndex
> Loop
>
> End Sub
>
>
> --
> Jeff

Hallo Jim

Loop << This is wrong! (No Do ..... Loop) delete Loop

End Sub

The code you posted cannot be the code you tested since with the final
'Loop' it cannot compile.

Also, the IsNull() test applied to any XL cell will always be false.
So, the If Ctr<>1 and ... will always be false and all that should
happen in your code is that XL should race through the I and J indices.

If you get that fixed...

You have to *Set* a range variable to a range. So, the code for
CbeRange=range(...) will fail at runtime as will the assignments to
MyRow and MyCol.

Also, the Find method may throw an error. You should anticipate that.

One more thing. You can simplify a lot of the code that you have
painstakingly typed. The MyRow and MyCol assignments can be simplified
to
Set MyRow=cells(iindex,2).resize(1,9) and
Set MyCol=cells(7,2).resize(9,1)

Finally, I haven't examined your algorithm to see if it will attain its
apparent goal -- and it look kinda simplistic -- but wouldn't some call
this cheating? Isn't the idea behind Sudoku to exercise your mind
afresh with each new puzzle? {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <1ACD394A-294B-415A-986D-86639BF43A8B@microsoft.com>,
Jeff@discussions.microsoft.com says...
> see anything wrong with this, it cause excel 2002 to "not respond" when the
> button is clicked
>
> Private Sub Button2_Click()
>
> Dim IIndex As Integer
> Dim JIndex As Integer
> Dim Tests As Integer
> Dim Fill As Integer
> Dim Ctr As Integer
> Dim IIxx As Integer
> Dim JIxx As Integer
> Dim CbeRange As Range
> Dim Myrow As Range
> Dim Mycol As Range
> Dim MC As Integer
> Dim MR As Integer
> Dim CB As Integer
>
> Ctr = 0
> For IIndex = 7 To 15
> For JIndex = 2 To 10
>
> If Ctr <> 1 And IsNull(Cells(IIndex, JIndex)) Then
>
>
> If 2 <= JIndex And JIndex <= 4 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("B7:D9")
> If 5 <= JIndex And JIndex <= 7 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("E7:G9")
> If 8 <= JIndex And JIndex <= 10 And 7 <= IIndex And IIndex <= 9 Then
> CbeRange = Range("H7:J9")
> If 2 <= JIndex And JIndex <= 4 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("B10:D12")
> If 5 <= JIndex And JIndex <= 7 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("E10:G12")
> If 8 <= JIndex And JIndex <= 10 And 10 <= IIndex And IIndex <= 12 Then
> CbeRange = Range("H10:J12")
> If 2 <= JIndex And JIndex <= 4 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("B13:D15")
> If 5 <= JIndex And JIndex <= 7 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("E13:G15")
> If 8 <= JIndex And JIndex <= 10 And 13 <= IIndex And IIndex <= 15 Then
> CbeRange = Range("H13:J15")
>
> If 7 = IIndex Then Myrow = Range("B7:J7")
> If 8 = IIndex Then Myrow = Range("B8:J8")
> If 9 = IIndex Then Myrow = Range("B9:J9")
> If 10 = IIndex Then Myrow = Range("B10:J10")
> If 11 = IIndex Then Myrow = Range("B11:J11")
> If 12 = IIndex Then Myrow = Range("B12:J12")
> If 13 = IIndex Then Myrow = Range("B13:J13")
> If 14 = IIndex Then Myrow = Range("B14:J14")
> If 15 = IIndex Then Myrow = Range("B15:J15")
>
> If 2 = JIndex Then Mycol = Range("B7:B15")
> If 3 = JIndex Then Mycol = Range("C7:C15")
> If 4 = JIndex Then Mycol = Range("D7:D15")
> If 5 = JIndex Then Mycol = Range("E7:E15")
> If 6 = JIndex Then Mycol = Range("F7:F15")
> If 7 = JIndex Then Mycol = Range("G7:G15")
> If 8 = JIndex Then Mycol = Range("H7:H15")
> If 9 = JIndex Then Mycol = Range("I7:I15")
> If 10 = JIndex Then Mycol = Range("J7:J15")
>
> Ctr = 0
> For Tests = 1 To 9
> MC = 0
> If Range(Mycol).Find(Tests, LookIn:=xlValues) Then MC = 1
> MR = 0
> If Range(Myrow).Find(Tests, LookIn:=xlValues) Then MR = 1
> CB = 0
> If Range(CbeRange).Find(Tests, LookIn:=xlValues) Then CB = 1
> If MC = 0 And MR = 0 And CB = 0 Then
> Ctr = Ctr + 1
> Fill = Tests
> JIxx = JIndex
> IIxx = IIndex
> End If
> Next Tests
>
> If Ctr = 1 Then
> Cells(IIxx, JIxx) = Fill
> Cells(IIxx, JIxx).Font.Bold = True
> Cells(IIxx, JIxx).Font.Color = 5
> End If
>
> End If
>
> Next JIndex
> Next IIndex
> Loop
>
> End Sub
>
>
>