Select Range by Condition in Cell

[mucah!t]

Hello all,

I need to select all ranges A:C where there is a "TRUE" mentioned in A
The following code only selects the first occurance [from the bottom]
Any ideas?

Sub test()
   Dim lastrow As Long, icell As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
    If Range("A" & icell) = True Then
        
        Range("A" & icell, "C" & icell).Select
    
    Else
    End If
Next icell
End Sub

 

[CharlesChuckieCharles]

Hello all,

I need to select all ranges A:C where there is a "TRUE" mentioned in A
The following code only selects the first occurance [from the bottom]
Any ideas?

Sub test()
   Dim lastrow As Long, icell As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
    If Range("A" & icell) = True Then
 
        Range("A" & icell, "C" & icell).Select
 
    Else
    End If
Next icell
End Sub

TRY THIS
this collects the corresponding cell addresses before selecting

Sub test()
Dim lastrow As Long, icell As Long
Dim sRanges As String
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For icell = 1 To lastrow
    If Range("A" & icell) = True Then
        If sRanges = "" Then
        sRanges = "C" & Trim(Str(icell))
        Else
        sRanges = sRanges & ",C" & Trim(Str(icell))
        End If
        
      '  Range("A" & icell, "C" & icell).Select
    
    End If
Next icell
Range(sRanges).Select

End Sub

 

[JackDanIce]

As requested, but without looping and error handler if not found:

Sub ILoveBallons ()
 
Dim i as Long
Dim iFound as Range
 
On Error GoTo Notfound
 
With Range("A1:A" & Rows.Count)
  i = .find(what:=True, After:=Range("A1"), LookIn:=xlValues).Row
End With
 
Range("A1:C" & i).Select
 
Notfound:
MsgBox "Cannot find, macro stopping"
 
End Sub

 

[mucah!t]

Hello Charles,

it gives an runtime error on the following line

Range(sRanges).Select

 

[mucah!t]

Hello Jack,

Your code selects range A1:C17 and then shows the messagbox "cannot find...."

Excel Workbook
	A	B	C
1		01/06/11 Wed	4
2		17
3
4
5	FALSE	1-Jan	2
6	FALSE	1-Jan	2
7	FALSE	1-Jan	2
8	FALSE	1-Jan	2
9	FALSE	1-Jan	2
10	FALSE	1-Jan	2
11	FALSE	2-Jan	3
12	FALSE	2-Jan	3
13	FALSE	2-Jan	3
14	FALSE	2-Jan	3
15	FALSE	2-Jan	3
16	FALSE	2-Jan	3
17	TRUE	1-Jun	4
18	TRUE	1-Jun	4
19	TRUE	1-Jun	4
WERKBLAD

Excel 2003

Cell Formulas
Range	Formula
C1	=WEEKDAY(B1)
C5	=WEEKDAY(2)
C6	=WEEKDAY(2)
C7	=WEEKDAY(2)
C8	=WEEKDAY(2)
C9	=WEEKDAY(2)
C10	=WEEKDAY(2)
C11	=WEEKDAY(3)
C12	=WEEKDAY(3)
C13	=WEEKDAY(3)
C14	=WEEKDAY(3)
C15	=WEEKDAY(3)
C16	=WEEKDAY(3)
C17	=WEEKDAY(4)
C18	=WEEKDAY(4)
C19	=WEEKDAY(4)
B2	=ROW(INDEX(C5:C46,MATCH(1,INDEX(--(C5:C46=C1),0),0)))
B5	=IF($B$2=ROW(A5),$B$1,IF(C5<>C4,B4+1,B4))
B6	=IF($B$2=ROW(A6),$B$1,IF(C6<>C5,B5+1,B5))
B7	=IF($B$2=ROW(A7),$B$1,IF(C7<>C6,B6+1,B6))
B8	=IF($B$2=ROW(A8),$B$1,IF(C8<>C7,B7+1,B7))
B9	=IF($B$2=ROW(A9),$B$1,IF(C9<>C8,B8+1,B8))
B10	=IF($B$2=ROW(A10),$B$1,IF(C10<>C9,B9+1,B9))
B11	=IF($B$2=ROW(A11),$B$1,IF(C11<>C10,B10+1,B10))
B12	=IF($B$2=ROW(A12),$B$1,IF(C12<>C11,B11+1,B11))
B13	=IF($B$2=ROW(A13),$B$1,IF(C13<>C12,B12+1,B12))
B14	=IF($B$2=ROW(A14),$B$1,IF(C14<>C13,B13+1,B13))
B15	=IF($B$2=ROW(A15),$B$1,IF(C15<>C14,B14+1,B14))
B16	=IF($B$2=ROW(A16),$B$1,IF(C16<>C15,B15+1,B15))
B17	=IF($B$2=ROW(A17),$B$1,IF(C17<>C16,B16+1,B16))
B18	=IF($B$2=ROW(A18),$B$1,IF(C18<>C17,B17+1,B17))
B19	=IF($B$2=ROW(A19),$B$1,IF(C19<>C18,B18+1,B18))
A5	=IF(MONTH(B5)=MONTH($B$1),TRUE,FALSE)
A6	=IF(MONTH(B6)=MONTH($B$1),TRUE,FALSE)
A7	=IF(MONTH(B7)=MONTH($B$1),TRUE,FALSE)
A8	=IF(MONTH(B8)=MONTH($B$1),TRUE,FALSE)
A9	=IF(MONTH(B9)=MONTH($B$1),TRUE,FALSE)
A10	=IF(MONTH(B10)=MONTH($B$1),TRUE,FALSE)
A11	=IF(MONTH(B11)=MONTH($B$1),TRUE,FALSE)
A12	=IF(MONTH(B12)=MONTH($B$1),TRUE,FALSE)
A13	=IF(MONTH(B13)=MONTH($B$1),TRUE,FALSE)
A14	=IF(MONTH(B14)=MONTH($B$1),TRUE,FALSE)
A15	=IF(MONTH(B15)=MONTH($B$1),TRUE,FALSE)
A16	=IF(MONTH(B16)=MONTH($B$1),TRUE,FALSE)
A17	=IF(MONTH(B17)=MONTH($B$1),TRUE,FALSE)
A18	=IF(MONTH(B18)=MONTH($B$1),TRUE,FALSE)
A19	=IF(MONTH(B19)=MONTH($B$1),TRUE,FALSE)

 

[CharlesChuckieCharles]

Hello Charles,

it gives an runtime error on the following line

Range(sRanges).Select

Apologies
Edit to this

If sRangeS <> "" Then
Range(sRanges).Select
End If

Then check that your TRUE's are actually TRUE

 

[mucah!t]

Charles,

agian the same error on the same line.
The "true's" are actually the result of a formula.
I am not sure if that could make any difference.
This is what the formula looks like:

Cell Formulas
Range	Formula
A5	=IF(MONTH(B5)=MONTH($B$1),TRUE,FALSE)

 

[CharlesChuckieCharles]

When you step through the code the sRanges variable should look something like "C1,C5,c17" ?

 

[mucah!t]

In the given example it shoudl select range A17:C19

 

[CharlesChuckieCharles]

My mistake i thought you wanted to select in all cells in C corresponding cells to where column a = true (which drew from the inferance of only doing the first cell problem)

So now i'm not sure what was wanted?