| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help please!
I am trying to read the column below, and if a cell is blank, I want to
populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 LOCATION# 0000000016 BAG# 1075 LOCATION# 0000000018 BAG# 9841 LOCATION# 0000000016 BAG# 7992 LOCATION# 0000000026 BAG# 9845 LOCATION# 0000000016 BAG# 7990 LOCATION# 0000000026 BAG# 2625 LOCATION# 0000000071 BAG# 1077 LOCATION# 0000000018 BAG# 0136 LOCATION# 0000000061 BAG# 0067 LOCATION# 0000000061 BAG# 0391 LOCATION# 0000000062 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro help please!
You need to create a loop that says something like
Dim x as Integer x = 1 Do Until '''put what you want here If ActiveCell.Value = "" Then ActiveCell.Value = x x = x + 1 End If ActiveCell.Offset(1,0).Activate Loop "pm" wrote: I am trying to read the column below, and if a cell is blank, I want to populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 LOCATION# 0000000016 BAG# 1075 LOCATION# 0000000018 BAG# 9841 LOCATION# 0000000016 BAG# 7992 LOCATION# 0000000026 BAG# 9845 LOCATION# 0000000016 BAG# 7990 LOCATION# 0000000026 BAG# 2625 LOCATION# 0000000071 BAG# 1077 LOCATION# 0000000018 BAG# 0136 LOCATION# 0000000061 BAG# 0067 LOCATION# 0000000061 BAG# 0391 LOCATION# 0000000062 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro help please!
This little macro will do that. I assumed your data is in Column A starting in row 2. HTH Otto Sub FillBlank() Dim rColA As Range Dim i As Range Dim c As Long c = 1 Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA If IsEmpty(i.Value) Then i.Value = c c = c + 1 End If Next i End Sub "pm" wrote in message ... I am trying to read the column below, and if a cell is blank, I want to populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 LOCATION# 0000000016 BAG# 1075 LOCATION# 0000000018 BAG# 9841 LOCATION# 0000000016 BAG# 7992 LOCATION# 0000000026 BAG# 9845 LOCATION# 0000000016 BAG# 7990 LOCATION# 0000000026 BAG# 2625 LOCATION# 0000000071 BAG# 1077 LOCATION# 0000000018 BAG# 0136 LOCATION# 0000000061 BAG# 0067 LOCATION# 0000000061 BAG# 0391 LOCATION# 0000000062 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro help please!
Thanks....That helped...I got it to work.
"akphidelt" wrote: You need to create a loop that says something like Dim x as Integer x = 1 Do Until '''put what you want here If ActiveCell.Value = "" Then ActiveCell.Value = x x = x + 1 End If ActiveCell.Offset(1,0).Activate Loop "pm" wrote: I am trying to read the column below, and if a cell is blank, I want to populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 LOCATION# 0000000016 BAG# 1075 LOCATION# 0000000018 BAG# 9841 LOCATION# 0000000016 BAG# 7992 LOCATION# 0000000026 BAG# 9845 LOCATION# 0000000016 BAG# 7990 LOCATION# 0000000026 BAG# 2625 LOCATION# 0000000071 BAG# 1077 LOCATION# 0000000018 BAG# 0136 LOCATION# 0000000061 BAG# 0067 LOCATION# 0000000061 BAG# 0391 LOCATION# 0000000062 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro help please!
Thanks. I'll try this one....looks more efficient than mine.
"Otto Moehrbach" wrote: This little macro will do that. I assumed your data is in Column A starting in row 2. HTH Otto Sub FillBlank() Dim rColA As Range Dim i As Range Dim c As Long c = 1 Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each i In rColA If IsEmpty(i.Value) Then i.Value = c c = c + 1 End If Next i End Sub "pm" wrote in message ... I am trying to read the column below, and if a cell is blank, I want to populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 LOCATION# 0000000016 BAG# 1075 LOCATION# 0000000018 BAG# 9841 LOCATION# 0000000016 BAG# 7992 LOCATION# 0000000026 BAG# 9845 LOCATION# 0000000016 BAG# 7990 LOCATION# 0000000026 BAG# 2625 LOCATION# 0000000071 BAG# 1077 LOCATION# 0000000018 BAG# 0136 LOCATION# 0000000061 BAG# 0067 LOCATION# 0000000061 BAG# 0391 LOCATION# 0000000062 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro help please!
In addition to what was already posted, this one skips the non-blanks:
Dim rng As Excel.Range Dim cell As Excel.Range Dim i As Long Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlan ks) i = 1 For Each cell In rng cell.Value = i i = i + 1 Next cell HTH, JP On Mar 19, 3:44*pm, pm wrote: I am trying to read the column below, and if a cell is blank, I want to populate it with a number and increment each subsequent blank after that. Thanks. Memo BAG# 9843 * * * *LOCATION# 0000000016 BAG# 1075 * * * *LOCATION# 0000000018 BAG# 9841 * * * *LOCATION# 0000000016 BAG# 7992 * * * *LOCATION# 0000000026 BAG# 9845 * * * *LOCATION# 0000000016 BAG# 7990 * * * *LOCATION# 0000000026 BAG# 2625 * * * *LOCATION# 0000000071 BAG# 1077 * * * *LOCATION# 0000000018 BAG# 0136 * * * *LOCATION# 0000000061 BAG# 0067 * * * *LOCATION# 0000000061 BAG# 0391 * * * *LOCATION# 0000000062 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
| My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
| using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Linear Mode
