help with array

[sa02000]

I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please?

This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet.

I am no expert by any means. Please help!

Sub Macro1()
Dim temp(1, 1000) As Integer
i = 0
previouscell = 0
For Each cell In Range("C:C")
currentcell = cell.Value
abc = previouscell + 1
If currentcell <> abc Then
temp(1, i) = currentcell
i = i + 1
Range("D" & i).Value = temp(1, i)
End If
previouscell = cell.Value

Next cell

End Sub


Thanks, Jay

[Biff]

Hi!

You can do this with a formula as long as the max number in the sequence is
<=65536.

Suppose your sequence is from 1 to 5000 with various numbers missing and is
in the range C1:C4000.

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX(ROW($1:$5000),SMALL(IF(COUNTIF(C$1:C$4000,ROW($1:$5000))=0,ROW($1:$5000)-ROW(A$1)+1),ROWS($1:1)))

Copy down until you get #NUM! errors.

Biff

"sa02000" <[email protected]> wrote in
message news:[email protected]...
>
> I have 4000 rows of data with numbers in it (only one column...column
> C). These numbers are consecutive but these have breaks in between. So
> I am trying to find out where those breaks are and output those points
> in a list. So here is a macro that I came up with...but these array
> doesn't seem to write out results.... can anybody help please?
>
> This macro tries to compare the two adjacent cells and if the
> difference between the values is greater than 1 then it copies that
> value in an array and writes that value in a cell in the same sheet.
>
> I am no expert by any means. Please help!
>
> Sub Macro1()
> Dim temp(1, 1000) As Integer
> i = 0
> previouscell = 0
> For Each cell In Range("C:C")
> currentcell = cell.Value
> abc = previouscell + 1
> If currentcell <> abc Then
> temp(1, i) = currentcell
> i = i + 1
> Range("D" & i).Value = temp(1, i)
> End If
> previouscell = cell.Value
>
> Next cell
>
> End Sub
>
>
> Thanks, Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile:
> http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=569539
>

[Biff]

P.S.

If you want an error trap: (makes the formula almost twice as long)

=IF(ROWS($1:1)<=SUMPRODUCT(--(ISNA(MATCH(ROW($1:$5000),C$1:C$4000,0)))),INDEX(ROW($1:$5000),SMALL(IF(COUNTIF(C$1:C$4000,ROW($1:$5000))=0,ROW($1:$5000)-ROW(A$1)+1),ROWS($1:1))),"")

Biff

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> You can do this with a formula as long as the max number in the sequence
> is <=65536.
>
> Suppose your sequence is from 1 to 5000 with various numbers missing and
> is in the range C1:C4000.
>
> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>
> =INDEX(ROW($1:$5000),SMALL(IF(COUNTIF(C$1:C$4000,ROW($1:$5000))=0,ROW($1:$5000)-ROW(A$1)+1),ROWS($1:1)))
>
> Copy down until you get #NUM! errors.
>
> Biff
>
> "sa02000" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I have 4000 rows of data with numbers in it (only one column...column
>> C). These numbers are consecutive but these have breaks in between. So
>> I am trying to find out where those breaks are and output those points
>> in a list. So here is a macro that I came up with...but these array
>> doesn't seem to write out results.... can anybody help please?
>>
>> This macro tries to compare the two adjacent cells and if the
>> difference between the values is greater than 1 then it copies that
>> value in an array and writes that value in a cell in the same sheet.
>>
>> I am no expert by any means. Please help!
>>
>> Sub Macro1()
>> Dim temp(1, 1000) As Integer
>> i = 0
>> previouscell = 0
>> For Each cell In Range("C:C")
>> currentcell = cell.Value
>> abc = previouscell + 1
>> If currentcell <> abc Then
>> temp(1, i) = currentcell
>> i = i + 1
>> Range("D" & i).Value = temp(1, i)
>> End If
>> previouscell = cell.Value
>>
>> Next cell
>>
>> End Sub
>>
>>
>> Thanks, Jay
>>
>>
>> --
>> sa02000
>> ------------------------------------------------------------------------
>> sa02000's Profile:
>> http://www.excelforum.com/member.php...o&userid=27747
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=569539
>>
>
>

[sa02000]

Thanks for reply Biff.
This seem to take too long for excel to calculate all the cells when I copy the formula down and then all it does is gives me odd numbers... 1, 3, 5, etc. in the cells........ Any suggestions. Wouldn't macro be lot faster anyway?

Jay

[Biff]

The formula works if implemented properly. If it's not working properly
you'd have to give me the EXACT DETAILS of your problem including the EXACT
formula you're using.

>Wouldn't macro be lot faster anyway?

Maybe, maybe not. I don't write code. (yet!)

Biff

"sa02000" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for reply Biff.
> This seem to take too long for excel to calculate all the cells when I
> copy the formula down and then all it does is gives me odd numbers...
> 1, 3, 5, etc. in the cells........ Any suggestions. Wouldn't macro be
> lot faster anyway?
>
> Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile:
> http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=569539
>

[JMB]

So you have a column of consecutive numbers, but some of those numbers are
missing and you want to know which ones are missing (listed in column D of
the same worksheet)?

Be sure to backup your work first. Be sure the worksheet w/your data is the
active sheet, change the range as needed, and change the 1 in the for loop to
whatever is the lowest number that is supposed to appear in your data set.


Sub test()
Dim lngMax As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000")
lngMax = Application.Max(rngData)
lngcount = 1

For i = 1 To lngMax
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i
lngcount = lngcount + 1
End If
Next i

End Sub


"sa02000" wrote:

>
> I have 4000 rows of data with numbers in it (only one column...column
> C). These numbers are consecutive but these have breaks in between. So
> I am trying to find out where those breaks are and output those points
> in a list. So here is a macro that I came up with...but these array
> doesn't seem to write out results.... can anybody help please?
>
> This macro tries to compare the two adjacent cells and if the
> difference between the values is greater than 1 then it copies that
> value in an array and writes that value in a cell in the same sheet.
>
> I am no expert by any means. Please help!
>
> Sub Macro1()
> Dim temp(1, 1000) As Integer
> i = 0
> previouscell = 0
> For Each cell In Range("C:C")
> currentcell = cell.Value
> abc = previouscell + 1
> If currentcell <> abc Then
> temp(1, i) = currentcell
> i = i + 1
> Range("D" & i).Value = temp(1, i)
> End If
> previouscell = cell.Value
>
> Next cell
>
> End Sub
>
>
> Thanks, Jay
>
>
> --
> sa02000
> ------------------------------------------------------------------------
> sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
> View this thread: http://www.excelforum.com/showthread...hreadid=569539
>
>

[Biff]

Yeah, that's a lot faster!

I'll be "hittin" the VBA books soon!

Biff

"JMB" <[email protected]> wrote in message
news:[email protected]...
> So you have a column of consecutive numbers, but some of those numbers are
> missing and you want to know which ones are missing (listed in column D of
> the same worksheet)?
>
> Be sure to backup your work first. Be sure the worksheet w/your data is
> the
> active sheet, change the range as needed, and change the 1 in the for loop
> to
> whatever is the lowest number that is supposed to appear in your data set.
>
>
> Sub test()
> Dim lngMax As Long
> Dim i As Long
> Dim rngData As Range
> Dim lngcount As Long
>
> Set rngData = Range("C1:C1000")
> lngMax = Application.Max(rngData)
> lngcount = 1
>
> For i = 1 To lngMax
> If Not IsNumeric(Application.Match(i, _
> rngData, 0)) Then
> Range("D" & lngcount).Value = i
> lngcount = lngcount + 1
> End If
> Next i
>
> End Sub
>
>
> "sa02000" wrote:
>
>>
>> I have 4000 rows of data with numbers in it (only one column...column
>> C). These numbers are consecutive but these have breaks in between. So
>> I am trying to find out where those breaks are and output those points
>> in a list. So here is a macro that I came up with...but these array
>> doesn't seem to write out results.... can anybody help please?
>>
>> This macro tries to compare the two adjacent cells and if the
>> difference between the values is greater than 1 then it copies that
>> value in an array and writes that value in a cell in the same sheet.
>>
>> I am no expert by any means. Please help!
>>
>> Sub Macro1()
>> Dim temp(1, 1000) As Integer
>> i = 0
>> previouscell = 0
>> For Each cell In Range("C:C")
>> currentcell = cell.Value
>> abc = previouscell + 1
>> If currentcell <> abc Then
>> temp(1, i) = currentcell
>> i = i + 1
>> Range("D" & i).Value = temp(1, i)
>> End If
>> previouscell = cell.Value
>>
>> Next cell
>>
>> End Sub
>>
>>
>> Thanks, Jay
>>
>>
>> --
>> sa02000
>> ------------------------------------------------------------------------
>> sa02000's Profile:
>> http://www.excelforum.com/member.php...o&userid=27747
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=569539
>>
>>

[Biff]

Hmmm....

Just thought of something.

> lngMax = Application.Max(rngData)

This seems to assume that the max value in the range is the upper boundry of
the sequence. What if the upper boundry is one of the missing values?

For example, the sequence is 1 to 110 but the max value in the range is 100.

Biff

"JMB" <[email protected]> wrote in message
news:[email protected]...
> So you have a column of consecutive numbers, but some of those numbers are
> missing and you want to know which ones are missing (listed in column D of
> the same worksheet)?
>
> Be sure to backup your work first. Be sure the worksheet w/your data is
> the
> active sheet, change the range as needed, and change the 1 in the for loop
> to
> whatever is the lowest number that is supposed to appear in your data set.
>
>
> Sub test()
> Dim lngMax As Long
> Dim i As Long
> Dim rngData As Range
> Dim lngcount As Long
>
> Set rngData = Range("C1:C1000")
> lngMax = Application.Max(rngData)
> lngcount = 1
>
> For i = 1 To lngMax
> If Not IsNumeric(Application.Match(i, _
> rngData, 0)) Then
> Range("D" & lngcount).Value = i
> lngcount = lngcount + 1
> End If
> Next i
>
> End Sub
>
>
> "sa02000" wrote:
>
>>
>> I have 4000 rows of data with numbers in it (only one column...column
>> C). These numbers are consecutive but these have breaks in between. So
>> I am trying to find out where those breaks are and output those points
>> in a list. So here is a macro that I came up with...but these array
>> doesn't seem to write out results.... can anybody help please?
>>
>> This macro tries to compare the two adjacent cells and if the
>> difference between the values is greater than 1 then it copies that
>> value in an array and writes that value in a cell in the same sheet.
>>
>> I am no expert by any means. Please help!
>>
>> Sub Macro1()
>> Dim temp(1, 1000) As Integer
>> i = 0
>> previouscell = 0
>> For Each cell In Range("C:C")
>> currentcell = cell.Value
>> abc = previouscell + 1
>> If currentcell <> abc Then
>> temp(1, i) = currentcell
>> i = i + 1
>> Range("D" & i).Value = temp(1, i)
>> End If
>> previouscell = cell.Value
>>
>> Next cell
>>
>> End Sub
>>
>>
>> Thanks, Jay
>>
>>
>> --
>> sa02000
>> ------------------------------------------------------------------------
>> sa02000's Profile:
>> http://www.excelforum.com/member.php...o&userid=27747
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=569539
>>
>>

[Biff]

Hmmm....

Just thought of something.

> lngMax = Application.Max(rngData)

This seems to assume that the max value in the range is the upper boundry of
the sequence. What if the upper boundry is one of the missing values?

For example, the sequence is 1 to 110 but the max value in the range is 100.

Biff

"JMB" <[email protected]> wrote in message
news:[email protected]...
> So you have a column of consecutive numbers, but some of those numbers are
> missing and you want to know which ones are missing (listed in column D of
> the same worksheet)?
>
> Be sure to backup your work first. Be sure the worksheet w/your data is
> the
> active sheet, change the range as needed, and change the 1 in the for loop
> to
> whatever is the lowest number that is supposed to appear in your data set.
>
>
> Sub test()
> Dim lngMax As Long
> Dim i As Long
> Dim rngData As Range
> Dim lngcount As Long
>
> Set rngData = Range("C1:C1000")
> lngMax = Application.Max(rngData)
> lngcount = 1
>
> For i = 1 To lngMax
> If Not IsNumeric(Application.Match(i, _
> rngData, 0)) Then
> Range("D" & lngcount).Value = i
> lngcount = lngcount + 1
> End If
> Next i
>
> End Sub
>
>
> "sa02000" wrote:
>
>>
>> I have 4000 rows of data with numbers in it (only one column...column
>> C). These numbers are consecutive but these have breaks in between. So
>> I am trying to find out where those breaks are and output those points
>> in a list. So here is a macro that I came up with...but these array
>> doesn't seem to write out results.... can anybody help please?
>>
>> This macro tries to compare the two adjacent cells and if the
>> difference between the values is greater than 1 then it copies that
>> value in an array and writes that value in a cell in the same sheet.
>>
>> I am no expert by any means. Please help!
>>
>> Sub Macro1()
>> Dim temp(1, 1000) As Integer
>> i = 0
>> previouscell = 0
>> For Each cell In Range("C:C")
>> currentcell = cell.Value
>> abc = previouscell + 1
>> If currentcell <> abc Then
>> temp(1, i) = currentcell
>> i = i + 1
>> Range("D" & i).Value = temp(1, i)
>> End If
>> previouscell = cell.Value
>>
>> Next cell
>>
>> End Sub
>>
>>
>> Thanks, Jay
>>
>>
>> --
>> sa02000
>> ------------------------------------------------------------------------
>> sa02000's Profile:
>> http://www.excelforum.com/member.php...o&userid=27747
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=569539
>>
>>

[JMB]

That is an excellent point. The OP will need to change it to whatever the
upper bound should be.

It shouldn't take you long to get VBA down (just be sure to follow examples
from people who are better at it than I am <g>).

Sub test()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000")
lngLower = 1 '<<<CHANGE
lngUpper = 5000 '<<<CHANGE
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i
lngcount = lngcount + 1
End If
Next i

End Sub





"Biff" wrote:

> Hmmm....
>
> Just thought of something.
>
> > lngMax = Application.Max(rngData)
>
> This seems to assume that the max value in the range is the upper boundry of
> the sequence. What if the upper boundry is one of the missing values?
>
> For example, the sequence is 1 to 110 but the max value in the range is 100.
>
> Biff
>
> "JMB" <[email protected]> wrote in message
> news:[email protected]...
> > So you have a column of consecutive numbers, but some of those numbers are
> > missing and you want to know which ones are missing (listed in column D of
> > the same worksheet)?
> >
> > Be sure to backup your work first. Be sure the worksheet w/your data is
> > the
> > active sheet, change the range as needed, and change the 1 in the for loop
> > to
> > whatever is the lowest number that is supposed to appear in your data set.
> >
> >
> > Sub test()
> > Dim lngMax As Long
> > Dim i As Long
> > Dim rngData As Range
> > Dim lngcount As Long
> >
> > Set rngData = Range("C1:C1000")
> > lngMax = Application.Max(rngData)
> > lngcount = 1
> >
> > For i = 1 To lngMax
> > If Not IsNumeric(Application.Match(i, _
> > rngData, 0)) Then
> > Range("D" & lngcount).Value = i
> > lngcount = lngcount + 1
> > End If
> > Next i
> >
> > End Sub
> >
> >
> > "sa02000" wrote:
> >
> >>
> >> I have 4000 rows of data with numbers in it (only one column...column
> >> C). These numbers are consecutive but these have breaks in between. So
> >> I am trying to find out where those breaks are and output those points
> >> in a list. So here is a macro that I came up with...but these array
> >> doesn't seem to write out results.... can anybody help please?
> >>
> >> This macro tries to compare the two adjacent cells and if the
> >> difference between the values is greater than 1 then it copies that
> >> value in an array and writes that value in a cell in the same sheet.
> >>
> >> I am no expert by any means. Please help!
> >>
> >> Sub Macro1()
> >> Dim temp(1, 1000) As Integer
> >> i = 0
> >> previouscell = 0
> >> For Each cell In Range("C:C")
> >> currentcell = cell.Value
> >> abc = previouscell + 1
> >> If currentcell <> abc Then
> >> temp(1, i) = currentcell
> >> i = i + 1
> >> Range("D" & i).Value = temp(1, i)
> >> End If
> >> previouscell = cell.Value
> >>
> >> Next cell
> >>
> >> End Sub
> >>
> >>
> >> Thanks, Jay
> >>
> >>
> >> --
> >> sa02000
> >> ------------------------------------------------------------------------
> >> sa02000's Profile:
> >> http://www.excelforum.com/member.php...o&userid=27747
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=569539
> >>
> >>
>
>
>

[JMB]

That is an excellent point. The OP will need to change it to whatever the
upper bound should be.

It shouldn't take you long to get VBA down (just be sure to follow examples
from people who are better at it than I am <g>).

Sub test()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("C1:C1000")
lngLower = 1 '<<<CHANGE
lngUpper = 5000 '<<<CHANGE
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("D" & lngcount).Value = i
lngcount = lngcount + 1
End If
Next i

End Sub





"Biff" wrote:

> Hmmm....
>
> Just thought of something.
>
> > lngMax = Application.Max(rngData)
>
> This seems to assume that the max value in the range is the upper boundry of
> the sequence. What if the upper boundry is one of the missing values?
>
> For example, the sequence is 1 to 110 but the max value in the range is 100.
>
> Biff
>
> "JMB" <[email protected]> wrote in message
> news:[email protected]...
> > So you have a column of consecutive numbers, but some of those numbers are
> > missing and you want to know which ones are missing (listed in column D of
> > the same worksheet)?
> >
> > Be sure to backup your work first. Be sure the worksheet w/your data is
> > the
> > active sheet, change the range as needed, and change the 1 in the for loop
> > to
> > whatever is the lowest number that is supposed to appear in your data set.
> >
> >
> > Sub test()
> > Dim lngMax As Long
> > Dim i As Long
> > Dim rngData As Range
> > Dim lngcount As Long
> >
> > Set rngData = Range("C1:C1000")
> > lngMax = Application.Max(rngData)
> > lngcount = 1
> >
> > For i = 1 To lngMax
> > If Not IsNumeric(Application.Match(i, _
> > rngData, 0)) Then
> > Range("D" & lngcount).Value = i
> > lngcount = lngcount + 1
> > End If
> > Next i
> >
> > End Sub
> >
> >
> > "sa02000" wrote:
> >
> >>
> >> I have 4000 rows of data with numbers in it (only one column...column
> >> C). These numbers are consecutive but these have breaks in between. So
> >> I am trying to find out where those breaks are and output those points
> >> in a list. So here is a macro that I came up with...but these array
> >> doesn't seem to write out results.... can anybody help please?
> >>
> >> This macro tries to compare the two adjacent cells and if the
> >> difference between the values is greater than 1 then it copies that
> >> value in an array and writes that value in a cell in the same sheet.
> >>
> >> I am no expert by any means. Please help!
> >>
> >> Sub Macro1()
> >> Dim temp(1, 1000) As Integer
> >> i = 0
> >> previouscell = 0
> >> For Each cell In Range("C:C")
> >> currentcell = cell.Value
> >> abc = previouscell + 1
> >> If currentcell <> abc Then
> >> temp(1, i) = currentcell
> >> i = i + 1
> >> Range("D" & i).Value = temp(1, i)
> >> End If
> >> previouscell = cell.Value
> >>
> >> Next cell
> >>
> >> End Sub
> >>
> >>
> >> Thanks, Jay
> >>
> >>
> >> --
> >> sa02000
> >> ------------------------------------------------------------------------
> >> sa02000's Profile:
> >> http://www.excelforum.com/member.php...o&userid=27747
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=569539
> >>
> >>
>
>
>