Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells skip blank cells, Excel 2000 & 2003
Hello,
How can the following macro be modified to skip blank cells: Sub NumberCellsValue() Dim LastRow As Long With ActiveSheet Columns("A:A").Select Selection.Insert Shift:=xlToRight With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Font.Bold = False Selection.ColumnWidth = 5 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Range("a1").Value = 1 With .Range("a2:a" & LastRow) .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)" .Value = .Value End With .Range("a:a").NumberFormat = "000" End With Range("A1:A3").ClearContents Range("A1").Select End Sub Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells skip blank cells, Excel 2000 & 2003
Why don't you explain what you want to do. The code you have posted won't do
very much of anything except throw error messages. "jfcby" wrote: Hello, How can the following macro be modified to skip blank cells: Sub NumberCellsValue() Dim LastRow As Long With ActiveSheet Columns("A:A").Select Selection.Insert Shift:=xlToRight With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Font.Bold = False Selection.ColumnWidth = 5 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row .Range("a1").Value = 1 With .Range("a2:a" & LastRow) .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)" .Value = .Value End With .Range("a:a").NumberFormat = "000" End With Range("A1:A3").ClearContents Range("A1").Select End Sub Thank you for your help, jfcby |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells skip blank cells, Excel 2000 & 2003
Hello JLGWhiz,
Thank you for your help! In my worksheet beginning with A4, if B4 = B5 Then A4 = 1 if B5 = B6 Then A5 = 2 if B6 isBlank Then A6 isBlank if B7 isNotEqual to B8 then A7 = 1 if B8 isNotEqual to B9 then A8 = 1 if B9 = B10 Then A9 = 2 if B10 = B11 Then A10 = 3 Thank you for your help, jfcby |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells skip blank cells, Excel 2000 & 2003
Hello JLGWhiz,
Thank you for your help! In my worksheet beginning with A4, if B4 = B5 Then A4 = 1 if B5 = B6 Then A5 = 2 if B6 isBlank Then A6 isBlank if B7 isNotEqual to B8 then A7 = 1 if B8 isNotEqual to B9 then A8 = 1 if B9 = B10 Then A9 = 2 if B10 = B11 Then A10 = 3 Thank you for your help, jfcby |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells skip blank cells, Excel 2000 & 2003
Hello JLGWhiz,
I apologize for not giving more details in my earlier post. Thank you for your help. In my worksheet my data looks like this: Col A is empty Col B B4 AIR COMP B5 AIR DRYER B6 CAB HEATER B7 CAB HEATER B8 CAB HEATER B9 CAB HEATER B10 B11 COIL, REHEAT B12 DKFTN B13 DKFTN B14 B15 Beginning with A4, if B4 = B5 Then A4 = 001 if B5 < B6 Then A5 = 001 if B6 = B7 Then A6 = 001 if B7 = B8 Then A7 = 002 if B8 = B9 Then A8 = 003 and A9 = 004 if B10 isBlank Then A10 isBlank if B11 < B12 Then A11 = 001 if B12 = B13 Then A12 = 001 and A13 = 002 if B14 isBlank Then A14 isBlank if B15 isBlank Then A15 isBlank What I need to do is number the items in column b that are the same begininng with 001 in Col A as described above skipping blank cells. I changed the above macro to: Sub NumberCellsValue2() 'Number cells 001 Dim LastRow As Long With ActiveSheet Columns("A:A").Select Selection.Insert Shift:=xlToRight With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Font.Bold = False Selection.ColumnWidth = 5 LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row For Each cell In .Range("A4:A" & LastRow) cell.Select If cell.Value = "" And cell.Offset(, 1).Value = "" Then GoTo NX If cell.Offset(, 1).Value = cell.Offset(1, 1).Value Then If cell.Offset(, 1).Value < cell.Offset(-1, 1).Value And _ cell.Offset(, 1).Value = cell.Offset(1, 1) Then cell.Value = 1 ElseIf cell.Offset(, 1).Value = cell.Offset(-1, 1) And _ cell.Offset(, 1) = cell.Offset(1, 1) Then cell.Value = cell.Offset(-1) + 1 End If ElseIf cell.Offset(, 1).Value = cell.Offset(-1, 1) Then cell.Value = cell.Offset(-1) + 1 ElseIf cell.Offset(, 1).Value = "" Then 'do nothing ElseIf cell.Offset(, 1).Value < cell.Offset(1, 1).Value Then cell.Value = 1 End If NX: Next 'End With .Range("a:a").NumberFormat = "000" End With Range("A1").Select End Sub Can this cade be modified without using so many if statements? If not, it works fine. Thank you for your help, jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row if all cells are blank, Excel 2000 & 2003 | Excel Discussion (Misc queries) | |||
Number Cells, Excel 2000 & 2003 | Excel Programming | |||
Number Cells As Cell Value Changes, Excel 2000 & 2003 | Excel Programming | |||
I want to copy a formula n Excel but skip any blank cells | Excel Worksheet Functions | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) |