I have a data set that contains over 66,000 lines. I would like to search for
each 'nan nan' then replace it with the number of entries that follow until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7', second
with '17'. I would also like to enter a '1' in the cell to the right of each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411

Your posting was not entirely clear as to whether "nan nan" was in a single
cell or if "nan" was in one cell and also in the neighboring cell. I assumed
"nan nan" was all in a single cell. With that in mind, this macro should do
what you want (first, set the column containing the "nan nan" in the
DataColumn constant statement replacing my assumed Column A)...

Sub NAN_NAN()
Dim X As Long, FirstRow As Long, LastRow As Long, PrevRow As Long
Dim C As Range, NanNan As Range
Const DataColumn As String = "A"
FirstRow = Columns(DataColumn).Find("nan nan", After:=Cells(Rows.Count, _
DataColumn), LookIn:=xlValues, LookAt:=xlWhole, _
SearchDirection:=xlNext, MatchCase:=False).Row
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
With Range(Cells(FirstRow, DataColumn), Cells(LastRow, DataColumn))
.Replace "nan nan", ""
Set NanNan = Union(.SpecialCells(xlCellTypeBlanks), _
Cells(LastRow + 1, DataColumn))
End With
For Each C In NanNan
If C.Row FirstRow Then
With Cells(PrevRow, DataColumn)
.Value = C.Row - PrevRow - 1
.Offset(0, 1).Value = 1
End With
End If
PrevRow = C.Row
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
I have a data set that contains over 66,000 lines. I would like to search
for
each 'nan nan' then replace it with the number of entries that follow
until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7',
second
with '17'. I would also like to enter a '1' in the cell to the right of
each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411

You could also do it with something like this:

Sub Nan2()
C = "A"

LastRow = Cells(Rows.Count, C).End(xlUp).Row

cpte = 0
For i = LastRow To 1 Step -1
If Cells(i, C).Value < "nan nan " Then
cpte = cpte + 1
Else
Cells(i, C).Value = cpte
Cells(i, C).Offset(0, 1) = 1
cpte = 0
End If
Next

End Sub

Mishell

"kjotro" a écrit dans le message de news:
...
I have a data set that contains over 66,000 lines. I would like to search
for
each 'nan nan' then replace it with the number of entries that follow
until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7',
second
with '17'. I would also like to enter a '1' in the cell to the right of
each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411

Rick

Your assumptions were correct and this seems to do exactly what I needed.
Thanks a million! Also could you recommend some intro material (texts, sites)
to begin learning how to write similar codes?

Thanks again
Kevin

"Rick Rothstein" wrote:

Your posting was not entirely clear as to whether "nan nan" was in a single
cell or if "nan" was in one cell and also in the neighboring cell. I assumed
"nan nan" was all in a single cell. With that in mind, this macro should do
what you want (first, set the column containing the "nan nan" in the
DataColumn constant statement replacing my assumed Column A)...

Sub NAN_NAN()
Dim X As Long, FirstRow As Long, LastRow As Long, PrevRow As Long
Dim C As Range, NanNan As Range
Const DataColumn As String = "A"
FirstRow = Columns(DataColumn).Find("nan nan", After:=Cells(Rows.Count, _
DataColumn), LookIn:=xlValues, LookAt:=xlWhole, _
SearchDirection:=xlNext, MatchCase:=False).Row
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
With Range(Cells(FirstRow, DataColumn), Cells(LastRow, DataColumn))
.Replace "nan nan", ""
Set NanNan = Union(.SpecialCells(xlCellTypeBlanks), _
Cells(LastRow + 1, DataColumn))
End With
For Each C In NanNan
If C.Row FirstRow Then
With Cells(PrevRow, DataColumn)
.Value = C.Row - PrevRow - 1
.Offset(0, 1).Value = 1
End With
End If
PrevRow = C.Row
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
I have a data set that contains over 66,000 lines. I would like to search
for
each 'nan nan' then replace it with the number of entries that follow
until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7',
second
with '17'. I would also like to enter a '1' in the cell to the right of
each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411


.

Actually, the approach Mishell used is much simpler than the method I used,
so you might want to consider using it instead. The only thing I didn't like
about her code is that she didn't Dim her variables; otherwise, the approach
is quite simple to follow. Here is Mishell's macro in which I used "more
friendly", at least to me, variable names and Dim'med them...

Sub Nan2()
Dim X As Long, LastRow As Long, CurrentCount As Long
Const DataColumn = "A"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
CurrentCount = 0
For X = LastRow To 1 Step -1
If Cells(X, DataColumn).Value < "nan nan" Then
CurrentCount = CurrentCount + 1
Else
Cells(X, DataColumn).Value = CurrentCount
Cells(X, DataColumn).Offset(0, 1) = 1
CurrentCount = 0
End If
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
Rick

Your assumptions were correct and this seems to do exactly what I needed.
Thanks a million! Also could you recommend some intro material (texts,
sites)
to begin learning how to write similar codes?

Thanks again
Kevin

"Rick Rothstein" wrote:

Your posting was not entirely clear as to whether "nan nan" was in a
single
cell or if "nan" was in one cell and also in the neighboring cell. I
assumed
"nan nan" was all in a single cell. With that in mind, this macro should
do
what you want (first, set the column containing the "nan nan" in the
DataColumn constant statement replacing my assumed Column A)...

Sub NAN_NAN()
Dim X As Long, FirstRow As Long, LastRow As Long, PrevRow As Long
Dim C As Range, NanNan As Range
Const DataColumn As String = "A"
FirstRow = Columns(DataColumn).Find("nan nan", After:=Cells(Rows.Count,
_
DataColumn), LookIn:=xlValues, LookAt:=xlWhole,
_
SearchDirection:=xlNext, MatchCase:=False).Row
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
With Range(Cells(FirstRow, DataColumn), Cells(LastRow, DataColumn))
.Replace "nan nan", ""
Set NanNan = Union(.SpecialCells(xlCellTypeBlanks), _
Cells(LastRow + 1, DataColumn))
End With
For Each C In NanNan
If C.Row FirstRow Then
With Cells(PrevRow, DataColumn)
.Value = C.Row - PrevRow - 1
.Offset(0, 1).Value = 1
End With
End If
PrevRow = C.Row
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
I have a data set that contains over 66,000 lines. I would like to
search
for
each 'nan nan' then replace it with the number of entries that follow
until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7',
second
with '17'. I would also like to enter a '1' in the cell to the right
of
each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411


.

Thank you Rick for your comments about my code.

Mishell


"Rick Rothstein" a écrit dans le
message de news: ...
Actually, the approach Mishell used is much simpler than the method I
used, so you might want to consider using it instead. The only thing I
didn't like about her code is that she didn't Dim her variables;
otherwise, the approach is quite simple to follow. Here is Mishell's macro
in which I used "more friendly", at least to me, variable names and
Dim'med them...

Sub Nan2()
Dim X As Long, LastRow As Long, CurrentCount As Long
Const DataColumn = "A"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
CurrentCount = 0
For X = LastRow To 1 Step -1
If Cells(X, DataColumn).Value < "nan nan" Then
CurrentCount = CurrentCount + 1
Else
Cells(X, DataColumn).Value = CurrentCount
Cells(X, DataColumn).Offset(0, 1) = 1
CurrentCount = 0
End If
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
Rick

Your assumptions were correct and this seems to do exactly what I needed.
Thanks a million! Also could you recommend some intro material (texts,
sites)
to begin learning how to write similar codes?

Thanks again
Kevin

"Rick Rothstein" wrote:

Your posting was not entirely clear as to whether "nan nan" was in a
single
cell or if "nan" was in one cell and also in the neighboring cell. I
assumed
"nan nan" was all in a single cell. With that in mind, this macro should
do
what you want (first, set the column containing the "nan nan" in the
DataColumn constant statement replacing my assumed Column A)...

Sub NAN_NAN()
Dim X As Long, FirstRow As Long, LastRow As Long, PrevRow As Long
Dim C As Range, NanNan As Range
Const DataColumn As String = "A"
FirstRow = Columns(DataColumn).Find("nan nan",
After:=Cells(Rows.Count, _
DataColumn), LookIn:=xlValues,
LookAt:=xlWhole, _
SearchDirection:=xlNext, MatchCase:=False).Row
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
With Range(Cells(FirstRow, DataColumn), Cells(LastRow, DataColumn))
.Replace "nan nan", ""
Set NanNan = Union(.SpecialCells(xlCellTypeBlanks), _
Cells(LastRow + 1, DataColumn))
End With
For Each C In NanNan
If C.Row FirstRow Then
With Cells(PrevRow, DataColumn)
.Value = C.Row - PrevRow - 1
.Offset(0, 1).Value = 1
End With
End If
PrevRow = C.Row
Next
End Sub

--
Rick (MVP - Excel)


"kjotro" wrote in message
...
I have a data set that contains over 66,000 lines. I would like to
search
for
each 'nan nan' then replace it with the number of entries that follow
until
the next 'nan nan'. Ex. the first 'nan nan' to be replaced with '7',
second
with '17'. I would also like to enter a '1' in the cell to the right
of
each
replaced value.
Thanks in advance. Kevin

nan nan
-89.34548 29.000135
-89.34636 28.998375
-89.346654 28.997788
-89.347534 28.997788
-89.347827 28.997495
-89.346654 28.996321
-89.341373 29.000135
nan nan
-89.424686 28.923276
-89.424686 28.924156
-89.423806 28.925036
-89.423806 28.925916
-89.422926 28.926796
-89.422926 28.927383
-89.421752 28.92885
-89.420872 28.92885
-89.419992 28.929437
-89.419112 28.929437
-89.417939 28.928263
-89.417939 28.927383
-89.418525 28.92709
-89.419112 28.92709
-89.423219 28.922983
-89.424099 28.922983
-89.424686 28.923276
nan nan
-89.393884 28.938237
-89.392417 28.939704
-89.39183 28.939411


.