Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum At Top And Move Cells Down in VBA
Hey,
Two questions, simple one first. How does one enter text into a cell from VBA? For example I just want to write "Totals" in H2, from the macro. Now the more complex question. Excel 2007, I have data in columns A to E, nothing in F, and data in G to K (all of these columns have headers). Throughout this process I want to leave the data in columns A through E untouched, not even moved down. So, in columns I, J, and K there are numerical values only, which I need to sum. Ideally, I would like those sums to be in I2, J2, and K2 respectively, but there's already data in those cells. Is there a way to sum columns I, J and K into cells I2 J2 K2, while moving only the data in columns G through K down? I'm unsure if it will help, but here is the macro I would like to get this code into, directly before the autoformat at the end if possible. Sub DataMove() RowCount = 1 For Each c In Range("B:B") If c.Value Like "*Hardwood Floors*" Or _ c.Value Like "*Type*" Or _ c.Value Like "*Oak Floors*" Or _ c.Value Like "*Tile*" Or _ c.Value Like "*Laminate*" Or _ c.Value Like "*Granite*" Or _ c.Value Like "*Other*" Then Cells(RowCount, "H").Value = c.Value Cells(RowCount, "I").Value = c.Offset(0, 1).Value Cells(RowCount, "J").Value = c.Offset(0, 2).Value Cells(RowCount, "K").Value = c.Offset(0, 3).Value Cells(RowCount, "G").Value = c.Offset(0, -1).Value RowCount = RowCount + 1 End If Next ActiveSheet.UsedRange.Select Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.UsedRange.AutoFormat End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum At Top And Move Cells Down in VBA
Range("H12").Value = "Totals"
"Jbm" wrote in message ... Hey, Two questions, simple one first. How does one enter text into a cell from VBA? For example I just want to write "Totals" in H2, from the macro. Now the more complex question. Excel 2007, I have data in columns A to E, nothing in F, and data in G to K (all of these columns have headers). Throughout this process I want to leave the data in columns A through E untouched, not even moved down. So, in columns I, J, and K there are numerical values only, which I need to sum. Ideally, I would like those sums to be in I2, J2, and K2 respectively, but there's already data in those cells. Is there a way to sum columns I, J and K into cells I2 J2 K2, while moving only the data in columns G through K down? I'm unsure if it will help, but here is the macro I would like to get this code into, directly before the autoformat at the end if possible. Sub DataMove() RowCount = 1 For Each c In Range("B:B") If c.Value Like "*Hardwood Floors*" Or _ c.Value Like "*Type*" Or _ c.Value Like "*Oak Floors*" Or _ c.Value Like "*Tile*" Or _ c.Value Like "*Laminate*" Or _ c.Value Like "*Granite*" Or _ c.Value Like "*Other*" Then Cells(RowCount, "H").Value = c.Value Cells(RowCount, "I").Value = c.Offset(0, 1).Value Cells(RowCount, "J").Value = c.Offset(0, 2).Value Cells(RowCount, "K").Value = c.Offset(0, 3).Value Cells(RowCount, "G").Value = c.Offset(0, -1).Value RowCount = RowCount + 1 End If Next ActiveSheet.UsedRange.Select Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.UsedRange.AutoFormat End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum At Top And Move Cells Down in VBA
Range("G1:K2").Insert shift:=xlDown
"Jbm" wrote in message ... Hey, Two questions, simple one first. How does one enter text into a cell from VBA? For example I just want to write "Totals" in H2, from the macro. Now the more complex question. Excel 2007, I have data in columns A to E, nothing in F, and data in G to K (all of these columns have headers). Throughout this process I want to leave the data in columns A through E untouched, not even moved down. So, in columns I, J, and K there are numerical values only, which I need to sum. Ideally, I would like those sums to be in I2, J2, and K2 respectively, but there's already data in those cells. Is there a way to sum columns I, J and K into cells I2 J2 K2, while moving only the data in columns G through K down? I'm unsure if it will help, but here is the macro I would like to get this code into, directly before the autoformat at the end if possible. Sub DataMove() RowCount = 1 For Each c In Range("B:B") If c.Value Like "*Hardwood Floors*" Or _ c.Value Like "*Type*" Or _ c.Value Like "*Oak Floors*" Or _ c.Value Like "*Tile*" Or _ c.Value Like "*Laminate*" Or _ c.Value Like "*Granite*" Or _ c.Value Like "*Other*" Then Cells(RowCount, "H").Value = c.Value Cells(RowCount, "I").Value = c.Offset(0, 1).Value Cells(RowCount, "J").Value = c.Offset(0, 2).Value Cells(RowCount, "K").Value = c.Offset(0, 3).Value Cells(RowCount, "G").Value = c.Offset(0, -1).Value RowCount = RowCount + 1 End If Next ActiveSheet.UsedRange.Select Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.UsedRange.AutoFormat End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum At Top And Move Cells Down in VBA
Patrick,
Thanks, was a simple solution to what I thought was a complex question. Sorry for asking what should probably be basics, but I spent hours with Excel Help and it was just too opaque for me to penetrate. Thanks again Patrick. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can references (to cells being sorted) move with the cells? | Setting up and Configuration of Excel | |||
Move cells according to value | Excel Discussion (Misc queries) | |||
Sorting cells: a list behind the cells do not move with the cell | Excel Discussion (Misc queries) | |||
Move 2 cells to right | Excel Discussion (Misc queries) | |||
How to move cells with VBA? | Excel Programming |