Prev: I'm Paste values stupid
Next: Simple Macro to Auto fill Cells
From: kjotro on 12 Feb 2010 00:20
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

From: Rick Rothstein on 12 Feb 2010 01:33
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" <kjotro(a)discussions.microsoft.com> wrote in message
news:6651FA78-12BA-47B2-9637-152FDA49D22A(a)microsoft.com...
>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
>

From: Mishell on 12 Feb 2010 05:38
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" <kjotro(a)discussions.microsoft.com> a �crit dans le message de news:
6651FA78-12BA-47B2-9637-152FDA49D22A(a)microsoft.com...
>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
>


 | 
Pages: 1
Prev: I'm Paste values stupid
Next: Simple Macro to Auto fill Cells