| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding a formula in macro
I have a column of items I want to count. The length of the column changes
by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Just count entries in the whole column:
msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Hi Tom, thanks! I would like the result to appear in the cell below, not a
message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Assumes no blank cells within the filled cells.
cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Maybe I'm not explaining this well, or maybe I don't know how to put this
code into my macro code. Thanks for your help Tom. This didn't work though. I am at the botton of a column and I need to count all the items in the column, move to the right 2 columns and sum those entries. The number of rows will vary from spreadsheet to spreadsheet. This is what I have so far. Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("B:B").ColumnWidth = 1.14 Columns("C:C").ColumnWidth = 2.14 Columns("E:E").ColumnWidth = 12.14 Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveCell.Offset(-1).Select Selection.Copy Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveSheet.Paste Columns(2).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "T" Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Cells(Rows.Count, 4).End(xlUp)(2).Value = _ ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-1261]C:R[-1]C)" ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:="C:\boa\apout.prn", FileFormat:= _ xlTextPrinter, CreateBackup:=False ActiveWindow.Close Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal End Sub Thanks again for your help, c "Tom Ogilvy" wrote: Assumes no blank cells within the filled cells. cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
I don't see anywhere in your code that you are trying to count cells. Your
code Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select puts you at the first blank cell after the data in column 4 (assuming the data has no blanks in it). (why would you need a count at this point?) You say you want to move right two columns and sum those entries, so activeCell.offset(0,2).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-2]:R[-1]C[-2])" -- Regards, Tom Ogilvy "cindy" wrote in message ... Maybe I'm not explaining this well, or maybe I don't know how to put this code into my macro code. Thanks for your help Tom. This didn't work though. I am at the botton of a column and I need to count all the items in the column, move to the right 2 columns and sum those entries. The number of rows will vary from spreadsheet to spreadsheet. This is what I have so far. Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("B:B").ColumnWidth = 1.14 Columns("C:C").ColumnWidth = 2.14 Columns("E:E").ColumnWidth = 12.14 Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveCell.Offset(-1).Select Selection.Copy Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveSheet.Paste Columns(2).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "T" Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Cells(Rows.Count, 4).End(xlUp)(2).Value = _ ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-1261]C:R[-1]C)" ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:="C:\boa\apout.prn", FileFormat:= _ xlTextPrinter, CreateBackup:=False ActiveWindow.Close Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal End Sub Thanks again for your help, c "Tom Ogilvy" wrote: Assumes no blank cells within the filled cells. cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Hi there, I'm actually creating a .prn file for ftp and the count of the
items is required in the footer of the file. So at the bottom of column d I need the count, and the bottom of column e in need the sum. Thanks for your help, you're wonderful, c "Tom Ogilvy" wrote: I don't see anywhere in your code that you are trying to count cells. Your code Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select puts you at the first blank cell after the data in column 4 (assuming the data has no blanks in it). (why would you need a count at this point?) You say you want to move right two columns and sum those entries, so activeCell.offset(0,2).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-2]:R[-1]C[-2])" -- Regards, Tom Ogilvy "cindy" wrote in message ... Maybe I'm not explaining this well, or maybe I don't know how to put this code into my macro code. Thanks for your help Tom. This didn't work though. I am at the botton of a column and I need to count all the items in the column, move to the right 2 columns and sum those entries. The number of rows will vary from spreadsheet to spreadsheet. This is what I have so far. Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("B:B").ColumnWidth = 1.14 Columns("C:C").ColumnWidth = 2.14 Columns("E:E").ColumnWidth = 12.14 Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveCell.Offset(-1).Select Selection.Copy Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveSheet.Paste Columns(2).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "T" Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Cells(Rows.Count, 4).End(xlUp)(2).Value = _ ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-1261]C:R[-1]C)" ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:="C:\boa\apout.prn", FileFormat:= _ xlTextPrinter, CreateBackup:=False ActiveWindow.Close Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal End Sub Thanks again for your help, c "Tom Ogilvy" wrote: Assumes no blank cells within the filled cells. cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select
activeCell.Value = Application.CountA(columns(4)) activeCell.offset(0,1).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-1]:R[-1]C[-1])" activeCell.Formula = ActiveCell.Value ' if you want to replace the formula with the value displayed. -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi there, I'm actually creating a .prn file for ftp and the count of the items is required in the footer of the file. So at the bottom of column d I need the count, and the bottom of column e in need the sum. Thanks for your help, you're wonderful, c "Tom Ogilvy" wrote: I don't see anywhere in your code that you are trying to count cells. Your code Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select puts you at the first blank cell after the data in column 4 (assuming the data has no blanks in it). (why would you need a count at this point?) You say you want to move right two columns and sum those entries, so activeCell.offset(0,2).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-2]:R[-1]C[-2])" -- Regards, Tom Ogilvy "cindy" wrote in message ... Maybe I'm not explaining this well, or maybe I don't know how to put this code into my macro code. Thanks for your help Tom. This didn't work though. I am at the botton of a column and I need to count all the items in the column, move to the right 2 columns and sum those entries. The number of rows will vary from spreadsheet to spreadsheet. This is what I have so far. Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("B:B").ColumnWidth = 1.14 Columns("C:C").ColumnWidth = 2.14 Columns("E:E").ColumnWidth = 12.14 Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveCell.Offset(-1).Select Selection.Copy Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveSheet.Paste Columns(2).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "T" Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Cells(Rows.Count, 4).End(xlUp)(2).Value = _ ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-1261]C:R[-1]C)" ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:="C:\boa\apout.prn", FileFormat:= _ xlTextPrinter, CreateBackup:=False ActiveWindow.Close Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal End Sub Thanks again for your help, c "Tom Ogilvy" wrote: Assumes no blank cells within the filled cells. cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adding a formula in macro
worked like a charm - thanks! :) c
"Tom Ogilvy" wrote: Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select activeCell.Value = Application.CountA(columns(4)) activeCell.offset(0,1).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-1]:R[-1]C[-1])" activeCell.Formula = ActiveCell.Value ' if you want to replace the formula with the value displayed. -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi there, I'm actually creating a .prn file for ftp and the count of the items is required in the footer of the file. So at the bottom of column d I need the count, and the bottom of column e in need the sum. Thanks for your help, you're wonderful, c "Tom Ogilvy" wrote: I don't see anywhere in your code that you are trying to count cells. Your code Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select puts you at the first blank cell after the data in column 4 (assuming the data has no blanks in it). (why would you need a count at this point?) You say you want to move right two columns and sum those entries, so activeCell.offset(0,2).Select ActiveCell.formulaR1C1 = "=Sum(R1C[-2]:R[-1]C[-2])" -- Regards, Tom Ogilvy "cindy" wrote in message ... Maybe I'm not explaining this well, or maybe I don't know how to put this code into my macro code. Thanks for your help Tom. This didn't work though. I am at the botton of a column and I need to count all the items in the column, move to the right 2 columns and sum those entries. The number of rows will vary from spreadsheet to spreadsheet. This is what I have so far. Rows("1:1").Select Selection.Delete Shift:=xlUp Columns("B:B").ColumnWidth = 1.14 Columns("C:C").ColumnWidth = 2.14 Columns("E:E").ColumnWidth = 12.14 Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveCell.Offset(-1).Select Selection.Copy Columns(1).SpecialCells(xlCellTypeBlanks).Cells(1) .Select ActiveSheet.Paste Columns(2).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "T" Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Cells(Rows.Count, 4).End(xlUp)(2).Value = _ ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=SUM(R[-1261]C:R[-1]C)" ActiveCell.Offset(-1).Select Selection.Copy ActiveCell.Offset(1).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Columns(4).SpecialCells(xlCellTypeBlanks).Cells(1) .Select Application.CutCopyMode = False ActiveWorkbook.SaveAs Filename:="C:\boa\apout.prn", FileFormat:= _ xlTextPrinter, CreateBackup:=False ActiveWindow.Close Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal Application.WindowState = xlMinimized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal End Sub Thanks again for your help, c "Tom Ogilvy" wrote: Assumes no blank cells within the filled cells. cnt = Application.countA(columns(5)) cells(cnt+1,5).Value = cnt otherwise cells(rows.count,5).End(xlup)(2).Value = _ application.CountA(columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... Hi Tom, thanks! I would like the result to appear in the cell below, not a message box. How do I move to that next available cell and put the result in it? thanks, cm "Tom Ogilvy" wrote: Just count entries in the whole column: msgbox Application.CountA(Columns(5)) -- Regards, Tom Ogilvy "cindy" wrote in message ... I have a column of items I want to count. The length of the column changes by spreadsheet. I would like the macro to start in the first blank cell and move up one cell and count all items to row 1. How do I code this when the originating cell address will change? thanks! cm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro adding formula to new sheet | Excel Discussion (Misc queries) | |||
| adding a formula to a macro | Excel Discussion (Misc queries) | |||
| Adding a character to a defined name with a formula or macro | Excel Discussion (Misc queries) | |||
| Adding a row with macro | Excel Programming | |||
| Adding row Macro | Excel Programming | |||
Linear Mode
