Excel Macro - Procedure Too Large

I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working. Any ideas?


Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False

Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
SelectNumber:
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0

.................................................(above statements repeat more than 300 times for different cells)................................


Application.EnableEvents = True

End Sub

saeeed this is the second time that you have not used Code Tags, please make sure that you understand them & use them in future.

Originally Posted by saeed9313

I am not doing it intentionally, how you do that?

Thanks

This is explained in the Forum Rules which you should have read.

To add Code Tags select the code in your post and then click # in the toolbar above the input box.

To edit a post you need to click the Edit button then click Go Advanced then follow the previous steps.

Threads merged and antoka05's post restored.

There is no reason to repeat code 300 times -- you only need one block of code and some additional logic. Without knowing what you're trying to do, I can't make a more specific suggestion.

What do you mean it repeats 300 times?

Are all the answers in Column E?

Assuming that you have all the answers entering into Column E and the results in G

Please Login or Register  to view this content.

This should make your problem more managable and the code short.

On sheet3 is a named range which contains all the valid target addresses that need to display the Input box.

Please Login or Register  to view this content.