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

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

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

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.