Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have the following code on a module:
This Worksheet is created dynamically and can have a different number of rows... I am trying to create a loop that can make this more dynamic so that instead of assuming this needs to be replicated 20 times, it could run through a loop until a cell value is = "" I can't quite get it... Here is the manual code... below that will be my attempt. The first loop could test a value from a different worksheet: src = srcProgramDataInputWs.Cells(i - 8, 2).Value (B3; then B15; B25, etc...) and wthen test = "" ------------------ If Target.Address = "$Q$12" Then If UCase(Range("R11")) = "TRUE" Then Range("R11") = "FALSE" Range("A16").Select Else Range("R11") = "TRUE" Range("A16").Select End If End If If Target.Address = "$Q$24" Then If UCase(Range("R23")) = "TRUE" Then Range("R23") = "FALSE" Range("A28").Select Else Range("R23") = "TRUE" Range("A28").Select End If End If If Target.Address = "$Q$36" Then If UCase(Range("R35")) = "TRUE" Then Range("R35") = "FALSE" Range("A40").Select Else Range("R35") = "TRUE" Range("A40").Select End If End If If Target.Address = "$Q$48" Then If UCase(Range("R47")) = "TRUE" Then Range("R47") = "FALSE" Range("A52").Select Else Range("R47") = "TRUE" Range("A52").Select End If End If If Target.Address = "$Q$60" Then If UCase(Range("R59")) = "TRUE" Then Range("R59") = "FALSE" Range("A64").Select Else Range("R59") = "TRUE" Range("A64").Select End If End If If Target.Address = "$Q$72" Then If UCase(Range("R71")) = "TRUE" Then Range("R71") = "FALSE" Range("A76").Select Else Range("R71") = "TRUE" Range("A76").Select End If End If If Target.Address = "$Q$84" Then If UCase(Range("R83")) = "TRUE" Then Range("R83") = "FALSE" Range("A88").Select Else Range("R83") = "TRUE" Range("A88").Select End If End If If Target.Address = "$Q$96" Then If UCase(Range("R95")) = "TRUE" Then Range("R95") = "FALSE" Range("A100").Select Else Range("R95") = "TRUE" Range("A100").Select End If End If If Target.Address = "$Q$108" Then If UCase(Range("R107")) = "TRUE" Then Range("R107") = "FALSE" Range("A112").Select Else Range("R107") = "TRUE" Range("A112").Select End If End If If Target.Address = "$Q$120" Then If UCase(Range("R119")) = "TRUE" Then Range("R119") = "FALSE" Range("A124").Select Else Range("R119") = "TRUE" Range("A124").Select End If End If If Target.Address = "$Q$132" Then If UCase(Range("R131")) = "TRUE" Then Range("R131") = "FALSE" Range("A136").Select Else Range("R131") = "TRUE" Range("A136").Select End If End If If Target.Address = "$Q$144" Then If UCase(Range("R143")) = "TRUE" Then Range("R143") = "FALSE" Range("A148").Select Else Range("R143") = "TRUE" Range("A148").Select End If End If If Target.Address = "$Q$156" Then If UCase(Range("R155")) = "TRUE" Then Range("R155") = "FALSE" Range("A160").Select Else Range("R155") = "TRUE" Range("A160").Select End If End If If Target.Address = "$Q$168" Then If UCase(Range("R167")) = "TRUE" Then Range("R167") = "FALSE" Range("A172").Select Else Range("R167") = "TRUE" Range("A172").Select End If End If If Target.Address = "$Q$180" Then If UCase(Range("R179")) = "TRUE" Then Range("R179") = "FALSE" Range("A184").Select Else Range("R179") = "TRUE" Range("A184").Select End If End If If Target.Address = "$Q$192" Then If UCase(Range("R191")) = "TRUE" Then Range("R191") = "FALSE" Range("A196").Select Else Range("R191") = "TRUE" Range("A196").Select End If End If If Target.Address = "$Q$204" Then If UCase(Range("R203")) = "TRUE" Then Range("R203") = "FALSE" Range("A208").Select Else Range("R203") = "TRUE" Range("A208").Select End If End If If Target.Address = "$Q$216" Then If UCase(Range("R215")) = "TRUE" Then Range("R215") = "FALSE" Range("A220").Select Else Range("R215") = "TRUE" Range("A220").Select End If End If If Target.Address = "$Q$228" Then If UCase(Range("R227")) = "TRUE" Then Range("R227") = "FALSE" Range("A232").Select Else Range("R227") = "TRUE" Range("A232").Select End If End If If Target.Address = "$Q$240" Then If UCase(Range("R239")) = "TRUE" Then Range("R239") = "FALSE" Range("A244").Select Else Range("R239") = "TRUE" Range("A244").Select End If End If ------------------ ------------------ Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim srcProgramDataInputWs As Worksheet Dim i As Integer Dim src As Variant Set srcProgramDataInputWs = Sheets("ProgramDataInput") src = srcProgramDataInputWs.Range("B3").Value i = 11 Do Until src = "" If Target.Address = "$Q$" & i + 1 Then If UCase(Range("R" & i)) = "TRUE" Then Range("R" & i) = "FALSE" Range("A" & i + 5).Select Else Range("R" & i) = "TRUE" Range("A" & i + 5).Select End If End If i = i + 12 src = srcProgramDataInputWs.Cells(i - 8, 2).Value Loop Cancel = True End Sub ------------------ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
dynamicaly linked themes | Excel Discussion (Misc queries) | |||
Dynamicaly create/copy a new row in another worksheet | Excel Worksheet Functions | |||
CAN YOU DYNAMICALY CHANGE VLOOKUP SEARCH RANGE? | Excel Discussion (Misc queries) | |||
I am Tring to lear how to create addins with excel 2000 were can . | Excel Programming | |||
VBA-How to insert vba code file in a project using coding rather than manual import | Excel Programming |