One solution solved one more to go€¦ Loop & Cell variables?
I'm staring a new question because "officially" the other one is solved...
(TRUE/FALSE by merely Clicking cell)

I was provided the following solution which works exactly as I need it.
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------
Problem is€¦ that that Rows 11-22 are replicated (sometimes 5 times maybe 20
times€¦ The first set of Rows include Cells Q12, R11, and maybe A13 if needed
to see if there are any more rows of data to test for€¦

I was wondering if you code a loop in Excel/VBA to cover looping and
referencing a cell buy a dynamic number.

(Im a mainframe programmer trying to get used to this VBA stuff€¦)
But something the looks like this:

-------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

RowR=11
RowQ=12
RowA=13
' \ / \ /
Do While ISNUMBER(A&RowA) = "FALSE"

If Target.Address = "$Q$&RowQ" Then
If UCase(Range("R&RowA")) = "TRUE" Then
Range("R&RowA") = "FALSE"
Else
Range("R&RowA") = "TRUE"
End If
End If

RowR=11
RowQ=12
RowA=13

End If
Cancel = True
End Sub
-----------------------

Anyone want to take a stab at this?
Thanks in advance if you can€¦

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

1) You need loop back statement
2) You need to change condition when looped back (RowA = RowA +1 ?)
3) Change A&RowA to "A" & RowA .....
4) delete End If before Cancel=True statement

give it a try

"CRayF" wrote:

One solution solved one more to go€¦ Loop & Cell variables?
I'm staring a new question because "officially" the other one is solved...
(TRUE/FALSE by merely Clicking cell)

I was provided the following solution which works exactly as I need it.
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------
Problem is€¦ that that Rows 11-22 are replicated (sometimes 5 times maybe 20
times€¦ The first set of Rows include Cells Q12, R11, and maybe A13 if needed
to see if there are any more rows of data to test for€¦

I was wondering if you code a loop in Excel/VBA to cover looping and
referencing a cell buy a dynamic number.

(Im a mainframe programmer trying to get used to this VBA stuff€¦)
But something the looks like this:

-------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

RowR=11
RowQ=12
RowA=13
' \ / \ /
Do While ISNUMBER(A&RowA) = "FALSE"

If Target.Address = "$Q$&RowQ" Then
If UCase(Range("R&RowA")) = "TRUE" Then
Range("R&RowA") = "FALSE"
Else
Range("R&RowA") = "TRUE"
End If
End If

RowR=11
RowQ=12
RowA=13

End If
Cancel = True
End Sub
-----------------------

Anyone want to take a stab at this?
Thanks in advance if you can€¦

I think I added the loop as directed (I think) but I still can't quite get
the cell reference down correct...

The original working code was:
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------

The trying to add the loop incrementing rows by 12...
But the cell reference syntax is not right.
----------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

RowR = 11
RowQ = 12
RowA = 13

Do While IsNumber("A" & RowA) < "FALSE"
If Target.Address = "$Q$" & RowQ Then
If UCase(Range("R" & RowR)) = "TRUE" Then
Range("R" & RowR) = "FALSE"
Else
Range("R" & RowR) = "TRUE"
End If
End If
RowR = RowR + 12
RowQ = RowQ + 12
RowA = RowA + 12
Exit Do

Cancel = True
End Sub
----------------------------


"PY & Associates" wrote:

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

1) You need loop back statement
2) You need to change condition when looped back (RowA = RowA +1 ?)
3) Change A&RowA to "A" & RowA .....
4) delete End If before Cancel=True statement

give it a try

"CRayF" wrote:

One solution solved one more to go€¦ Loop & Cell variables?
I'm staring a new question because "officially" the other one is solved...
(TRUE/FALSE by merely Clicking cell)

I was provided the following solution which works exactly as I need it.
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------
Problem is€¦ that that Rows 11-22 are replicated (sometimes 5 times maybe 20
times€¦ The first set of Rows include Cells Q12, R11, and maybe A13 if needed
to see if there are any more rows of data to test for€¦

I was wondering if you code a loop in Excel/VBA to cover looping and
referencing a cell buy a dynamic number.

(Im a mainframe programmer trying to get used to this VBA stuff€¦)

Anyone want to take a stab at this?
Thanks in advance if you can€¦

This works although I am not convinced it does what you want as I am unsure
about that

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RowR As Long, RowQ As Long, RowA As Long
RowR = 11
RowQ = 12
RowA = 13

Do While Range("A" & RowA).Value = True
If UCase(Range("R" & RowR)) = True Then
Range("R" & RowR) = False
Else
Range("R" & RowR) = True
End If
RowR = RowR + 12
RowQ = RowQ + 12
RowA = RowA + 12
Loop

End Sub

--
HTH

Bob Phillips

"CRayF" wrote in message
...
I think I added the loop as directed (I think) but I still can't quite get
the cell reference down correct...

The original working code was:
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------

The trying to add the loop incrementing rows by 12...
But the cell reference syntax is not right.
----------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

RowR = 11
RowQ = 12
RowA = 13

Do While IsNumber("A" & RowA) < "FALSE"
If Target.Address = "$Q$" & RowQ Then
If UCase(Range("R" & RowR)) = "TRUE" Then
Range("R" & RowR) = "FALSE"
Else
Range("R" & RowR) = "TRUE"
End If
End If
RowR = RowR + 12
RowQ = RowQ + 12
RowA = RowA + 12
Exit Do

Cancel = True
End Sub
----------------------------


"PY & Associates" wrote:

Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]

Loop

1) You need loop back statement
2) You need to change condition when looped back (RowA = RowA +1 ?)
3) Change A&RowA to "A" & RowA .....
4) delete End If before Cancel=True statement

give it a try

"CRayF" wrote:

One solution solved one more to go. Loop & Cell variables?
I'm staring a new question because "officially" the other one is
solved...
(TRUE/FALSE by merely Clicking cell)

I was provided the following solution which works exactly as I need
it.
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub
-------------------------
Problem is. that that Rows 11-22 are replicated (sometimes 5 times
maybe 20
times. The first set of Rows include Cells Q12, R11, and maybe A13 if
needed
to see if there are any more rows of data to test for.

I was wondering if you code a loop in Excel/VBA to cover looping and
referencing a cell buy a dynamic number.

(I'm a mainframe programmer trying to get used to this VBA stuff.)

Anyone want to take a stab at this?
Thanks in advance if you can.

Something's not quite right...

What Im looking for is that when Q12 is selected, the R11 is set to TRUE,
when it is selected again, it is set to FALSE. This worked:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub

On the Worksheet, I may copy Rows (11-22), sometimes a few times, sometimes
a lot. I want the same clicking on its €œQ€� cell (Q12, Q24, Q36...) depending
on how many times I copy the rows to toggle it's "R" cell (R11, R23, R35) .
The loop can end when the €œA€� row, (A13, A25, A37) is tested NOT to have a
value.
Rows are in multiples of 12€¦
Target.Address = "$Q$12" and Range("R11") (BTW€¦ A13 is a number)
Target.Address = "$Q$24" and Range("R23") (A25 is a number)
Target.Address = "$Q$36" and Range("R35") (A37 is a number)
Etc€¦

I tried the code in the last reply but it does not set the value of R to
TRUE/FALSE with the €œQ€� cell is selected.

I was just hopping to turn this into a loop so that the number of rows I
copy can be more dynamic€¦ Manually entering the code below works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

If Target.Address = "$Q$24" Then
If UCase(Range("R23")) = "TRUE" Then
Range("R23") = "FALSE"
Else
Range("R23") = "TRUE"
End If
End If

If Target.Address = "$Q$36" Then
If UCase(Range("R35")) = "TRUE" Then
Range("R35") = "FALSE"
Else
Range("R35") = "TRUE"
End If
End If

If Target.Address = "$Q$48" Then
If UCase(Range("R47")) = "TRUE" Then
Range("R47") = "FALSE"
Else
Range("R47") = "TRUE"
End If
End If

If Target.Address = "$Q$60" Then
If UCase(Range("R59")) = "TRUE" Then
Range("R59") = "FALSE"
Else
Range("R59") = "TRUE"
End If
End If

Cancel = True
End Sub

,,,etc,,,