find header then replace header with number of entries below heade

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

0
Utf
2/12/2010 5:20:01 AM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
905 Views

Similar Articles

[PageSpeed] 53

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@discussions.microsoft.com> wrote in message 
news:6651FA78-12BA-47B2-9637-152FDA49D22A@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
> 

0
Rick
2/12/2010 6:33:08 AM
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@discussions.microsoft.com> a �crit dans le message de news: 
6651FA78-12BA-47B2-9637-152FDA49D22A@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
> 


0
Mishell
2/12/2010 10:38:56 AM
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" <kjotro@discussions.microsoft.com> wrote in message 
> news:6651FA78-12BA-47B2-9637-152FDA49D22A@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
> > 
> 
> .
> 
0
Utf
2/12/2010 3:08:01 PM
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" <kjotro@discussions.microsoft.com> wrote in message 
news:B55CFFB2-DF15-4084-AD99-9490D67E982E@microsoft.com...
> 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" <kjotro@discussions.microsoft.com> wrote in message
>> news:6651FA78-12BA-47B2-9637-152FDA49D22A@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
>> >
>>
>> .
>> 

0
Rick
2/12/2010 3:37:54 PM
Thank you Rick for your comments about my code.

Mishell


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> a �crit dans le 
message de news: uFVxTl$qKHA.5736@TK2MSFTNGP05.phx.gbl...
> 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" <kjotro@discussions.microsoft.com> wrote in message 
> news:B55CFFB2-DF15-4084-AD99-9490D67E982E@microsoft.com...
>> 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" <kjotro@discussions.microsoft.com> wrote in message
>>> news:6651FA78-12BA-47B2-9637-152FDA49D22A@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
>>> >
>>>
>>> .
>>>
> 


0
Mishell
2/12/2010 10:59:56 PM
Reply:

Similar Artilces:

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Find Duplicate names and delate
Dear experts, I have a small doubt could you clarify that??? That is I find duplicate name but I want to delete one name only, if I filter DUPLICATE….. both names are showing… 1. Select the range of data including the header. You need to have headers for these columns 2. From menu Data>Filter>Advanced Filter>Copy to another location 3. In 'copy to' specify the target cell and check 'Unique records only' 4. Click OK will give you the unique list -- Jacob "Find Duplicate names and delate" wrote: > Dear experts, > I have a small ...

Finding a Median
I'm trying to write a query that will return a median for various values taken from a previous query. I've seen some suggestions in my searching, but I haven't been able to get them working. They are also all from before 2003 and refer to Access 97 and 2000. Has any functionality been added to 2003 for this? Or is there a non- code-based way to do it? I've seen it suggested to write a code to open the query, sort it, find the total number of records, divide it in half, then seek out the middle record using that value. I'm still very green when it comes to code, though...

Find a Value the first Time It Occurs
I have a row of values that shows the total cumulative number of sales of items by month. Occasionally, there may be no sales in a month for an item so the cumulative value would stay the same for more than one month. I want to select a number in the row the first time it occurs and not select it if it repeats. What are you wanting to do with the info? To return position (column number) of number 1234 within row 2: =MATCH(1234,2:2,0) A formula that signals it's the first occurence: =COUNTIF($A2:A2,A2)=1 This could be used in a helper row, or as a conditional format f...

Add space after each numbered or bulleted item
I need to make rather long lists of paragraphs that are several lines long. The numbered list feature forces a new number and a new line upon hitting enter. this is expected behavior. However, I need a space between the items to make each item stand out. In Word if I hit enter again, a new number is issued as expected. However if I arrow up to the first empty space and hit the backspace, the number will be removed, the following number will be changed to next sequential number and the empty line remain. Publisher, however insists on starting a new number sequence. Is there any way to c...

how do I delete numbers without deleting formulas
In excel 2003 how do I delete a column of numbers without deleting the formulas in the column? F5>Special>Constants. Use the option buttons to select what you wish to highlight for deleting>Ok Edit>Clear Contents. Gord Dibben Excel MVP On Sat, 8 Oct 2005 13:13:02 -0700, "Emmaus junkie" <Emmaus junkie@discussions.microsoft.com> wrote: >In excel 2003 how do I delete a column of numbers without deleting the >formulas in the column? ...

outlook error: entry point ont found
hello i am having problems with outlook ( im running xp pro ) when i try to run outlook i get this error message: Microsoft outlook. outlook EXE entry point not found. the procedure entry point getiUMS could not be located in the dynamic link library. ive uninstalled /installed again ,but to no avail. any help will be greatly appreciated. Thankyou very much . Regards georgie ...

how do i get negative numbers in brackets in Excel
i find i cannot enter negative numbers in accounts in brackets but this is the accounting convention is it hidden in excel or do i need a plug in Hi Maverick, Enter numbers using the minus symbol, but format your cells: Format => Cells => Number [tab] and in the Type box, enter "#,##0.00_ ;[Red](#,##0.00) " (without the quotation marks). Ewan. "maverick accountant" wrote: > i find i cannot enter negative numbers in accounts in brackets > but this is the accounting convention > is it hidden in excel > or do i need a plug in ...

Find/Replace in RichEdit 2.0
I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my CRichEditDoc/View application so that I can use the ITextDocument interface and can do things such as suspend/resume the Redo buffer. Problem is, now the Find/Replace dialogs don't seem to do anything. If I revert back to RichEdit 1.0 they do! What's going on? BUMP! adriangibbons@yahoo.co.uk (Adrian Gibbons) wrote in message news:<34a2acd5.0404130713.123dbaab@posting.google.com>... > I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my > CRichEditDoc/View application...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message news:1184612089.486737.144020@n60g2000hse.googlegroups.com... > My program takes a name from sheet3 goes to sheet1 to Find the na...

Let's pick a replacement for this (Access) newsgroup 2
There were two big threads on the original 6/1/10 post in the General Questions and New User sections. The gist of the original post was: Folks have recommended various sites to replace this (Access) newsgroup for when poor-listener Microsoft drops it. Why don't we pick a replacement to give it the "critical mass" to fully replace this one? If interested, please respond to this with your recommendation. Also, if you feel like it, in case this goes dead, send me an email with your email address at North9000 at gmail dot com and I'll try to collect / send out...

Replacing data in Pivot Table causes Field name change
Good morning, I have a pivot table report that uses about 1200 rows of data on a neighboring page. Today I produced new monthly data and pasted it on top of the old data. When I do this the report's left most column field names all change from FieldName to FieldName2. When I look in the drop down box for that field it seems to have duplicated the fields when I pasted the new data. I'm able to work around it by changing the displayed lables from FieldName2 to FieldName"space""space". I can't change to just FieldName because that is already in use. M...

Finding an event
Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button, a new window (in same workbook) opens with a new sheet. My problem is that excel does not seem to have any events for close of window if there are multiple windows in a workbook. Can someone help Peter Peter, That triggers the Workbook_WindowActivate event, you can use that. HTH Bob "Peter Ostermann" wrote in message news:i9m5v8$7bv$02$1@news.t-online.com... Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

Cash receipts Entry
Hello, Is it possible to setup a General Ledger Account during entry in Cash Receipts?. I have a cheque where the amount is $107 where $105 is the amount and the $2 is the bank charges and I wanted to put the $2 on the bank charges account. If there is a setup I need to do, please let me know Thank You Kindly Why don't you just use 2 lines in the distribution? Credit one account $105 and the bank charges account $2? Or am I missing something? Is this under Transactions, Financial, Bank Transactions:Enter receipt? kingjack wrote: > Hello, > > Is it possible to setup ...

Remove Header Once Report Displays?
Hi All, I am trying to figure a report issue. I have a report that utilizes the subreport feature. When the subreport loads it is ok but once the subreport goes onto a 2nd page the headers are all out of wack. The header contains the main report which is quite confusing since fields are not the same. Can my second page only contain the subreport headers? Thanks. It isn't clear what you mean by "The header contains the main report which is quite confusing since fields are not the same". A subreport contains nothing from the main report. When you mention "...

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Restore ability to enter 20 character PO Numbers
With the 1.3R release, RMS will not allow PO Numbers longer than 14 characters. Before the 1.3R release, RMS allowed PO Numbers up to 20 characters. After installing the 1.3R release I reported this defect. The support team responded that this change was by design. They claimed that the change was made to work around a defect - "we had a bug with inter-store inventory transfers erroring out when the PO number is greater than 14 characters". This type of quick and easy fix (crippling the UI to work around a defect) is just plain wrong for at least a couple of reasons. 1) C...

Sum if Condition is Equal in Range Date and find column
I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

WinFax replacement
I wrote an Access app years ago for a client and it uses WinFax pro to send out invoices. They need to upgrade but WinFax is gone thanks to the wisdom (LOL) of Symantec. Does anyone know of a good replacement that can be referenced from vba and will allow us to send out Access reports? Thanks, Keith I'm moving this to the microsoft.public.access newsgroup. There's not enough activity here for this to get any attention. If you see this here and have an answer, please answer it there. Thanks. "Keith G Hicks" <krh@comcast.net> wrote in messag...

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

How to reduce the number of shortcuts/links in the recent documents list?
When I look into the folder: C:\Documents and Settings\<username>\recent then there are currently approx 20 recent documents listed. How can I permanently reduce the number or even disable this list? Cindy Cindy Parker wrote: > When I look into the folder: > > C:\Documents and Settings\<username>\recent > > then there are currently approx 20 recent documents listed. > > How can I permanently reduce the number or even disable this list? > > Cindy > Right click on the Start button and choose Properties --> Customize ...

How do I find the out of office reply?
My out of office reply is missing from tools. How can I retrieve it? Out of Office reply <Out of Office reply@discussions.microsoft.com> wrote: > My out of office reply is missing from tools. How can I retrieve it? Unless you are using an Exchange server, you will not have the Out of Office Assistant. See this: http://www.slipstick.com/rules/autoreply.htm -- Brian Tillman ...

Conditional Number Formatting & Selective Summing
Attached is the workbook I need help with. From what I have bee discovering from the Excel Macro-VBA Editor-Help screens, what I wan to do should require fairly simple VBA code. However, I have no VB coding experience or skills (the VBA code in Module 3 was gleaned fro the Excel Tips Forum. Thank you Harlan Grove) Sheets �Run� through �Run (30)� are identical, except that the cel formulas on sheets 2-30 are only active if the active sheet i �Enabled� by the user and will only populate with pulled data if al preceding sheets are �Enabled�. The nature of the workbook is such tha a user will alw...

VBA Multi Number-Letter Sort
I'm sorting a list of old in-stock items, example: GEARS / 1/8_spur 1/4_spur rack_1/4pitch. It was all put into a single column. Now I could count left/ middle etc and send it to a different sheet then sort the sheet etc etc etc etc. I would be very happy if I could find a link to a source of vba code I could change or use just to sort things like "3rd # 8 before 3rd # 4" or "W18x21 after W8x10" . Can I do this simply with vba, I keep looking but it does not look promising. just wandering thanks rentapen ...