Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
I am trying to create an macro that will evaluate text in a column B for eg.
If any text data exist in B1 make the count 1 and put the count number in column A. If text data exist in B2 make the count 2 and and so on. If for example B4 does not have text, I want the macro to skip to row B5 and continue the count. The number of rows varies so I want to stop counting when it reaches the last row of the file. Data is always in column C is until the very last row. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Do you really need a macro? If I understand your question correctly, you can
put this formula in A1 and copy down for as many rows as you ever expect to have data in... =IF(B1="","",COUNTA(B$1:B1)) Using this formula would make the numbering automatic without your having to remember to run the macro as the data in Column B changes. By the way, I did not see a reason why you mentioned Column C in your write up.... it appears to have nothing to do with your question. -- Rick (MVP - Excel) "Stenbeck" wrote in message ... I am trying to create an macro that will evaluate text in a column B for eg. If any text data exist in B1 make the count 1 and put the count number in column A. If text data exist in B2 make the count 2 and and so on. If for example B4 does not have text, I want the macro to skip to row B5 and continue the count. The number of rows varies so I want to stop counting when it reaches the last row of the file. Data is always in column C is until the very last row. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
However, if you really want to do this with a macro, here is one you can
use... Sub NumberColBItems() Dim X As Long, LastRow As Long, Index As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For X = 1 To LastRow If Len(.Cells(X, "B").Value) 0 Then Index = Index + 1 .Cells(X, "A").Value = Index End If Next End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Do you really need a macro? If I understand your question correctly, you can put this formula in A1 and copy down for as many rows as you ever expect to have data in... =IF(B1="","",COUNTA(B$1:B1)) Using this formula would make the numbering automatic without your having to remember to run the macro as the data in Column B changes. By the way, I did not see a reason why you mentioned Column C in your write up.... it appears to have nothing to do with your question. -- Rick (MVP - Excel) "Stenbeck" wrote in message ... I am trying to create an macro that will evaluate text in a column B for eg. If any text data exist in B1 make the count 1 and put the count number in column A. If text data exist in B2 make the count 2 and and so on. If for example B4 does not have text, I want the macro to skip to row B5 and continue the count. The number of rows varies so I want to stop counting when it reaches the last row of the file. Data is always in column C is until the very last row. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Rick,
Yes, I did need the macro. It worked beautifully. I apolgize for taking so long to say thank you, and providing and update. S "Rick Rothstein" wrote: Do you really need a macro? If I understand your question correctly, you can put this formula in A1 and copy down for as many rows as you ever expect to have data in... =IF(B1="","",COUNTA(B$1:B1)) Using this formula would make the numbering automatic without your having to remember to run the macro as the data in Column B changes. By the way, I did not see a reason why you mentioned Column C in your write up.... it appears to have nothing to do with your question. -- Rick (MVP - Excel) "Stenbeck" wrote in message ... I am trying to create an macro that will evaluate text in a column B for eg. If any text data exist in B1 make the count 1 and put the count number in column A. If text data exist in B2 make the count 2 and and so on. If for example B4 does not have text, I want the macro to skip to row B5 and continue the count. The number of rows varies so I want to stop counting when it reaches the last row of the file. Data is always in column C is until the very last row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |