ExcelBanter - Need one more thing

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Need one more thing (https://www.excelbanter.com/excel-programming/372788-need-one-more-thing.html)

Need one more thing

How do I take colums 3 and 5 of each row and add them in colum F to the
maching row?

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rw,"C"))
End With
rng.offset(0,3).Formula = "=Sum(R[-3]C,R[-1]C)"

--
Regards,
Tom Ogilvy

"jln via OfficeKB.com" wrote:

How do I take colums 3 and 5 of each row and add them in colum F to the
maching row?

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

hit the send too soon

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rows.count,"C").end(x lup))
End With
rng.offset(0,3).FormulaR1C1 = "=Sum(R[-3]C,R[-1]C)"

if you mean for Worksheets("NetPILIQ")

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 as Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
..AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
..Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
set rng6 = .Range(.Cells(6,3),.Cells(rows.count,3).end(xlup))
End With
rng6.offset(0,3).FormulaR1C1 = "=Sum(R[-3]C,R[-1]C)"
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote:

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rw,"C"))
End With
rng.offset(0,3).Formula = "=Sum(R[-3]C,R[-1]C)"

--
Regards,
Tom Ogilvy

"jln via OfficeKB.com" wrote:

How do I take colums 3 and 5 of each row and add them in colum F to the
maching row?

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

Make this your last 4 lines:
With Worksheets("NetPILIQ")
.Range("F6").Formula = "=SUM("C6,E6")"
.Range("F6", Range("E6").End(xlDown)).Offset(0, 1).FillDown
End With

Mike F
"jln via OfficeKB.com" <u25956@uwe wrote in message
news:663af2fb9d908@uwe...
How do I take colums 3 and 5 of each row and add them in colum F to the
maching row?

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

When I run this it add every other in column F. I need it to add C and E
and place the total in F

Tom Ogilvy wrote:
hit the send too soon

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rows.count,"C").end(x lup))
End With
rng.offset(0,3).FormulaR1C1 = "=Sum(R[-3]C,R[-1]C)"

if you mean for Worksheets("NetPILIQ")

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 as Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
set rng6 = .Range(.Cells(6,3),.Cells(rows.count,3).end(xlup))
End With
rng6.offset(0,3).FormulaR1C1 = "=Sum(R[-3]C,R[-1]C)"
End Sub

rw = 6 ' ????
With Worksheets("SomeWorksheet")
[quoted text clipped - 38 lines]

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

Sorry, had too many quotes in the formula:
Make this your last 4 lines:
With Worksheets("NetPILIQ")
.Range("F6").Formula = "=SUM(C6,E6)"
.Range("F6", Range("E6").End(xlDown)).Offset(0, 1).FillDown
End With

Mike F

"Mike Fogleman" wrote in message
m...
Make this your last 4 lines:
With Worksheets("NetPILIQ")
.Range("F6").Formula = "=SUM("C6,E6")"
.Range("F6", Range("E6").End(xlDown)).Offset(0, 1).FillDown
End With

Mike F
"jln via OfficeKB.com" <u25956@uwe wrote in message
news:663af2fb9d908@uwe...
How do I take colums 3 and 5 of each row and add them in colum F to the
maching row?

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200609/1

Need one more thing

got my rows and columns reversed. try this:

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
..AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
..Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
Set rng6 = .Range(.Cells(5, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
rng6.Offset(0, 3).FormulaR1C1 = "=Sum(RC[-3],RC[-1])"
End Sub

--
Regards,
Tom Ogilvy

Need one more thing

Thanks TOm for the Help WIth this it works great.

Tom Ogilvy wrote:
got my rows and columns reversed. try this:

Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range

With Worksheets("NetPILIQ")
.Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
.AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:="0"
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
.Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 1).End(xlUp)(2)
rng5.Resize(1, 5).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
Set rng6 = .Range(.Cells(5, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
rng6.Offset(0, 3).FormulaR1C1 = "=Sum(RC[-3],RC[-1])"
End Sub

--
Message posted via http://www.officekb.com