Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro hangs my excel (not responding)
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 921
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Macro hangs my excel (not responding)
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Macro hangs my excel (not responding)
Hallo Jim

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

End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,071
Default Macro hangs my excel (not responding)
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 ,
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro recorded... tabs & file names changed, macro hangs Steve Excel Worksheet Functions 3 October 30th 09 11:41 AM
MS Query - Add Table - Hangs/Not Responding Mathew P Bennett Excel Discussion (Misc queries) 0 August 4th 08 07:39 PM
Macro is not responding Len Case Excel Worksheet Functions 5 January 11th 08 06:18 PM
Macro hangs up often but sometimes works fine Jeff Excel Worksheet Functions 3 June 13th 06 01:01 PM
Macro causes Excel to "not responding" Mick[_3_] Excel Programming 3 September 13th 03 01:48 AM


All times are GMT +1. The time now is 11:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement