Need one more thing

  • Thread starter jln via OfficeKB.com
  • Start date
J

jln via OfficeKB.com

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
 
T

Tom Ogilvy

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)"
 
T

Tom Ogilvy

hit the send too soon

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rows.count,"C").end(xlup))
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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Mike Fogleman

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
 
J

jln via OfficeKB.com

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 said:
hit the send too soon

rw = 6 ' ????
With Worksheets("SomeWorksheet")
set rng = .Range(.Cells(rw,"C"),.Cells(rows.count,"C").end(xlup))
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")
Click to expand...
[quoted text clipped - 38 lines]
Click to expand...
 
M

Mike Fogleman

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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
 
J

jln via OfficeKB.com

Thanks TOm for the Help WIth this it works great.

Tom said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with auto filter 0
Creating a total line 4
Help with autofilter 1
Help with my cut and paste code 1
Extract Text in braquet and list of terms from 2 Docs 3
End process at last row of data 3
create a loop so i don't have to keep retyping? 4
Highlight a cell after update in a specific range 2

Top