Private Sub Worksheet_SelectionChange(ByVal Target As Range) Olayında Fazla Group aldırma

[cocoa35]

Merhaba arkadaşlar, Çalışmış olduğum bir excel kitapçığında bir sayfaya çeşitli "Group" lar getirmek istiyorum. Aşağıda örneği bulunan formül ile bu işlemi yaptırıyorum. Ancak bir sorun oluştu şöyleki; Aşağıdaki formülde ( Örnek olarak bir group koydum ancak gerçeğinde group sayıları fazla) "If/End If " en fazla 113 adet işlem yapıyor. 113 adetten fazla If/End If olduğunda aşağıdaki örnek'te görüldüğü şekilde "Procedure Too Large" sinyali veriyor. Benim daha fazla "group" belirlemem gerekiyor! bunun bir çaresi varmı acaba? Bu konuda yardımınızı istiyorum.Teşekkürler.

Örnek:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sayfa105.Range("J16") = 1 Then

ActiveSheet.Shapes("Group 227").Visible = False

ActiveSheet.Shapes("Group 183").Visible = False

ActiveSheet.Shapes("Group 146").Visible = False

ActiveSheet.Shapes("Group 113").Visible = True

Else

If Sayfa105.Range("J16") = 2 Then

ActiveSheet.Shapes("Group 225").Visible = False

ActiveSheet.Shapes("Group 30").Visible = False

ActiveSheet.Shapes("Group 22").Visible = True

ActiveSheet.Shapes("Group 15").Visible = True



Else

If Sayfa105.Range("J16") = 3 Then

ActiveSheet.Shapes("Group 225").Visible = False

ActiveSheet.Shapes("Group 30").Visible = True

ActiveSheet.Shapes("Group 22").Visible = True

ActiveSheet.Shapes("Group 15").Visible = True



Else

If Sayfa105.Range("J16") = 4 Then

ActiveSheet.Shapes("Group 225").Visible = True

ActiveSheet.Shapes("Group 30").Visible = True

ActiveSheet.Shapes("Group 22").Visible = True

ActiveSheet.Shapes("Group 15").Visible = True

Else

If Sayfa105.Range("J16") = 5 Then

ActiveSheet.Shapes("Group 225").Visible = True

ActiveSheet.Shapes("Group 30").Visible = True

ActiveSheet.Shapes("Group 22").Visible = True

ActiveSheet.Shapes("Group 15").Visible = True

End If

End If

End If

End If

End If
If Sayfa18.Range("C13") = 0 And Sayfa105.Range("J619") = 1 Then

ActiveSheet.Shapes("Group 4744").Visible = True

ActiveSheet.Shapes("Group 4111").Visible = False

ActiveSheet.Shapes("Group 4754").Visible = False

ActiveSheet.Shapes("Group 2412").Visible = True

ActiveSheet.Shapes("Group 4117").Visible = False

ActiveSheet.Shapes("Group 4115").Visible = False

Else

If Sayfa18.Range("C13") = 0 And Sayfa105.Range("J619") = 2 Then

ActiveSheet.Shapes("Group 4744").Visible = True

ActiveSheet.Shapes("Group 4111").Visible = True

ActiveSheet.Shapes("Group 4754").Visible = False

ActiveSheet.Shapes("Group 2412").Visible = True

ActiveSheet.Shapes("Group 4117").Visible = True

ActiveSheet.Shapes("Group 4115").Visible = False

Else

If Sayfa18.Range("C13") = 0 And Sayfa105.Range("J619") = 3 Then

ActiveSheet.Shapes("Group 4744").Visible = True

ActiveSheet.Shapes("Group 4111").Visible = True

ActiveSheet.Shapes("Group 4754").Visible = True

ActiveSheet.Shapes("Group 2412").Visible = True

ActiveSheet.Shapes("Group 4117").Visible = True

ActiveSheet.Shapes("Group 4115").Visible = True

Else

If Sayfa18.Range("C13") = 0 And Sayfa105.Range("J619") > 3 Then

ActiveSheet.Shapes("Group 4744").Visible = True

ActiveSheet.Shapes("Group 4111").Visible = True

ActiveSheet.Shapes("Group 4754").Visible = True

ActiveSheet.Shapes("Group 2412").Visible = True

ActiveSheet.Shapes("Group 4117").Visible = True

ActiveSheet.Shapes("Group 4115").Visible = True

End If

End If

End If

End If

End Sub

 

[cocoa35]

mg ve union formülünü uygulamak istiyorum bir konuda takıldım farklı sayfalarda bulunan hücre adları aşağıda nasıl yazılıyor? Ürnek

Set rng1 = Range("Sayfa1(N8),N26,Sayfa1(N41:N43),N47,Sayfa5(N136),N147:N148,N153,N163,C164,N201,N212,N216,N292,N297,N300,N318,N322:N323,N334,N336,N342,N382:N391")

Set rng2 = Range("Sayfa3(L391),N402,N411,N415,L415,N462,N471:N474,N479:N480,N486:N492,N501:N512,N541:N546,N553:N557,N565,N619,N685:N686,N691:N692")

Set rng3 = Range("N696:N697,Sayfa100(N701:N703),N767:N780,N784:N785,N802,N837:N838,N883,N946,N951:N954,N958:N960")

Şeklinde olurmu?