What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

I have tried the below with around 12 numbers satisfying the condition
scattered within the range..and found to be working fine...Just put a message
box to see what strAddress returns..

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next

End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

Just one thing which I noticed is that the below line should be

For Each cell In rngTemp

'instead of
For Each cell In ActiveSheet.UsedRange

But i dont see any probs other than that...Try in the activesheet itself and
see

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

I have tried the below with around 12 numbers satisfying the condition
scattered within the range..and found to be working fine...Just put a message
box to see what strAddress returns..

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next

End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

The code:

Sub Mcr1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then
For Each cell In rngTemp
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next
End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

still results in a message:

Run-time error '1004':

Method 'Range' of object '_Global' failed

with the text:

Range(Mid(strAddress, 2)).Select

highlighted.
--
Do check "Yes" if this post is helpful,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Just one thing which I noticed is that the below line should be

For Each cell In rngTemp

'instead of
For Each cell In ActiveSheet.UsedRange

But i dont see any probs other than that...Try in the activesheet itself and
see

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

I have tried the below with around 12 numbers satisfying the condition
scattered within the range..and found to be working fine...Just put a message
box to see what strAddress returns..

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next

End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

I have just noticed that if the first few rows are FULL of numbers the code
fails. Any particular reason?
--
Best Regards,

Faraz


"Faraz A. Qureshi" wrote:

The code:

Sub Mcr1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then
For Each cell In rngTemp
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next
End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

still results in a message:

Run-time error '1004':

Method 'Range' of object '_Global' failed

with the text:

Range(Mid(strAddress, 2)).Select

highlighted.
--
Do check "Yes" if this post is helpful,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Just one thing which I noticed is that the below line should be

For Each cell In rngTemp

'instead of
For Each cell In ActiveSheet.UsedRange

But i dont see any probs other than that...Try in the activesheet itself and
see

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

I have tried the below with around 12 numbers satisfying the condition
scattered within the range..and found to be working fine...Just put a message
box to see what strAddress returns..

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next

End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz

Works for me...Interesting..

--I have modified and referred the sheet and change .Select to .Activate.Try
the below..
--Does it work if you have only 2 or 3 number in the range?

Sub Mcr1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Sheets("Sheet1").Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then
For Each cell In rngTemp
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next
End If
rngTemp.Parent.Activate
If strAddress < "" Then Sheets("Sheet1").Range(Mid(strAddress, 2)).Activate
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

The code:

Sub Mcr1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then
For Each cell In rngTemp
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next
End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

still results in a message:

Run-time error '1004':

Method 'Range' of object '_Global' failed

with the text:

Range(Mid(strAddress, 2)).Select

highlighted.
--
Do check "Yes" if this post is helpful,
Best Regards,

Faraz


"Jacob Skaria" wrote:

Just one thing which I noticed is that the below line should be

For Each cell In rngTemp

'instead of
For Each cell In ActiveSheet.UsedRange

But i dont see any probs other than that...Try in the activesheet itself and
see

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

I have tried the below with around 12 numbers satisfying the condition
scattered within the range..and found to be working fine...Just put a message
box to see what strAddress returns..

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:K30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
End If
Next

End If
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

Thanx Jacob,

However, Error 1004, with
Range(Mid(strAddress, 2)).Select
highlighted is being faced.
--
Best Regards,

Faraz


"Jacob Skaria" wrote:

Faraz, you can try using COUNTIF() in from to check whether any number
exits..Advantages are..

--you can avoid the looping if there are no numbers...
--you can Exit the loop once the count is reached.

Sub Macro1()
Dim strAddress As String, cell As Range
Dim rngTemp As Range
Set rngTemp = Range("A1:J30")
If WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 10) - _
WorksheetFunction.CountIf(rngTemp, "" & (10 ^ 10) * 100) 0 Then

For Each cell In ActiveSheet.UsedRange
If cell.Value = (10 ^ 10) * 10 And _
cell.Value < (10 ^ 10) * 100 Then _
strAddress = strAddress & "," & cell.Address
Next
End If

If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Again

One way is to check the cell value and store the address in a string and
select at the end. There should be a much better way.

Sub Macro()
Dim strAddress As String, cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Value 100000000000# Then _
strAddress = strAddress & "," & cell.Address
Next
If strAddress < "" Then Range(Mid(strAddress, 2)).Select
End Sub

PS: You can modify the condition to suit

If this post helps click Yes
---------------
Jacob Skaria


"Faraz A. Qureshi" wrote:

What code would:
1. Find & Select cells;
2. With NUMBERS;
3. Which are of 12 digits, i.e. = 100000000000 & <=999999999999?
--
Best Regards,

Faraz