Modify range in VBA

[Helixal]

I need help to modify the range statement in a macro. As it stands I have
arbitraraly set the range to 400 rows. I want to change it so that the
range of rows ends with the last row containing data in column B.


I recorded the following macro to work to sum columns 4 and 7 up to the
point where the name changes in
collumn 2 based on the following formulas in cols H and I row 2:

H=D2+SUMIF($B$1:B1,B2,$D$11)
I=G2+SUMIF($B$1:B1,B2,$G$1:G1)



Sub Net1()
'
' Net1 Macro
' Macro recorded 5/2/2012 by Allen
'

'
Range("H1").Select
ActiveCell.FormulaR1C1 = "NET QNT."
Range("I1").Select
ActiveCell.FormulaR1C1 = "NET TOT."
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-4]+SUMIF(R1C2:R[-1]C[-6],RC[-6],R1C4:R[-1]C[-4])"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H400"), Type:=xlFillDefault
Range("H2:H400").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=RC[-2]+SUMIF(R1C2:R[-1]C[-7],RC[-7],R1C7:R[-1]C[-2])"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I400"), Type:=xlFillDefault
Range("I2:I400").Select
End Sub

 

[Claus Busch]

Hi,

Am Thu, 3 May 2012 14:19:35 -0400 schrieb Helixal:

I need help to modify the range statement in a macro. As it stands I have
arbitraraly set the range to 400 rows. I want to change it so that the
range of rows ends with the last row containing data in column B.

try it this way:
Sub Net_1()
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H1") = "NET QNT."
Range("I1") = "NET TOT."
Range("H2").Formula = "=D2+Sumif($B$1:B1,B2,$D$11)"
Range("I2").Formula = "=G2+Sumif($B$1:B1,B2,$G$1:G1)"
Range("H2:I2").AutoFill Destination:=Range("H2:I" & LRow)
End Sub


Regards
Claus Busch

 

[Helixal]

Claus,

This is brilliant. It works perfectly. Many thanks.

Is there an easy way of accomplishing the same result but without the
formulas (only the numbers) remaing in H and I?
Best regards,
Al

 

[Claus Busch]

Hi,

Am Thu, 3 May 2012 19:12:04 -0400 schrieb Helixal:

Is there an easy way of accomplishing the same result but without the
formulas (only the numbers) remaing in H and I?

you have to copy the formulas and paste them as values:

Sub Net_1()
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H1") = "NET QNT."
Range("I1") = "NET TOT."
Range("H2").Formula = "=D2+Sumif($B$1:B1,B2,$D$11)"
Range("I2").Formula = "=G2+Sumif($B$1:B1,B2,$G$1:G1)"
Range("H2:I2").AutoFill Destination:=Range("H2:I" & LRow)
Range("H2:I" & LRow).Copy
Range("H2").PasteSpecial xlPasteValues
End Sub

Regards
Claus Busch

 

[Helixal]

Claus,
Thanks again.
This does the trick.

We added the two new rows in the first blank columns H and I. It would be
more useful if the Summations of column D were placed next to it in column
E. The summation of old column G (new H) would still go in column I. I
tried doing this by inserting a column at E and adjusting the formulas but
the ranges gave me a problem.

One last thing I'd like to do, and this may be guilding the lilly, is to
Highlight (with 25% grey shading) a row from A to I when that is the last
row of a sequence of names in column B. Lets say that the rows 2,3 say
Google in column B and rows 4,5,6 say IBM. Then I'd like row 3 and row 6
to be highlighted. These are the final NET totals before the routine starts
to sum again.

Best regards,
Al

 

[Claus Busch]

Hi,

Am Fri, 4 May 2012 14:55:21 -0400 schrieb Helixal:

We added the two new rows in the first blank columns H and I. It would be
more useful if the Summations of column D were placed next to it in column
E. The summation of old column G (new H) would still go in column I. I
tried doing this by inserting a column at E and adjusting the formulas but
the ranges gave me a problem.

One last thing I'd like to do, and this may be guilding the lilly, is to
Highlight (with 25% grey shading) a row from A to I when that is the last
row of a sequence of names in column B. Lets say that the rows 2,3 say
Google in column B and rows 4,5,6 say IBM. Then I'd like row 3 and row 6
to be highlighted. These are the final NET totals before the routine starts
to sum again.

I hope i understand your problem.
Let it work like it did and then move columns H and I.
Test the macro on a copy of your workbook. The CF will be coloring your
rows:

Sub Net_1()
Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H1") = "NET QNT."
Range("I1") = "NET TOT."
Range("H2").Formula = "=D2+Sumif($B$1:B1,B2,$D$11)"
Range("I2").Formula = "=G2+Sumif($B$1:B1,B2,$G$1:G1)"
Range("H2:I2").AutoFill Destination:=Range("H2:I" & LRow)
Range("H2:I" & LRow).Copy
Range("H2").PasteSpecial xlPasteValues
Columns("H").Cut
Columns("E").Insert Shift:=xlToRight
Columns("I").Cut
Columns("F").Insert Shift:=xlToRight

With Range("A2:I100")
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$B3<>$B2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
End With
End Sub


Regards
Claus Busch

 

[Helixal]

I modified the the macro to get the rows correct. Only the NET.QNT row
needed to be moved over to column E. The second part of the macro is
returning an error.

Run Time error 1004:Application-defined or object defined error.

The following statement is highlighted:
..FormatConditions.Add Type:=xlExpression, _
Formula1:="=$B3<>$B2"

Here is the modified macro:

Sub net5()


Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("H1") = "NET QNT."
Range("I1") = "NET TOT."
Range("H2").Formula = "=D2+Sumif($B$1:B1,B2,$D$11)"
Range("I2").Formula = "=G2+Sumif($B$1:B1,B2,$G$1:G1)"
Range("H2:I2").AutoFill Destination:=Range("H2:I" & LRow)
Range("H2:I" & LRow).Copy
Range("H2").PasteSpecial xlPasteValues
Columns("H").Cut
Columns("E").Insert Shift:=xlToRight
' Columns("I").Cut
' Columns("F").Insert Shift:=xlToRight

With Range("A2:I100")
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$B3<>$B2"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
End With





End Sub

 

[Claus Busch]

Hi,

Am Fri, 4 May 2012 16:04:42 -0400 schrieb Helixal:

The following statement is highlighted:
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$B3<>$B2"

what Excel version do you use?
Try the conditional part with following code:
With Range("A2:I100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$B3<>$B2"
.FormatConditions(1).Interior.ColorIndex = 15
End With


Regards
Claus Busch

 

[Helixal]

Claus,
I think we have it. I am using Excel 2003 sp3.
I already had the rows shaded manually. I see that you added the delete
format conditions which did delete the manual shading. ( Did this interfer
with the Macro?) I also had some cell formating of font bolding and cell
outlining which were not affected by the delete format conditions statement.

I also changed the range statement from:

With Range("A2:I100") to With Range("A2:I" & LRow)
Which seems to work.

Thank you,
Al

 

[Claus Busch]

Hi Al,

Am Fri, 4 May 2012 17:00:19 -0400 schrieb Helixal:

I think we have it. I am using Excel 2003 sp3.
I already had the rows shaded manually. I see that you added the delete
format conditions which did delete the manual shading. ( Did this interfer
with the Macro?) I also had some cell formating of font bolding and cell
outlining which were not affected by the delete format conditions statement.

the manual shading did not interfere with the macro. The first code did
not work because it was for xl2007 or later.
Glad you bring it to work.


Regards
Claus Busch