Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need one more thing
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,092
Default 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





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
not sure if i'm using the right thing Tiffany Excel Worksheet Functions 4 September 19th 08 05:21 PM
For/Next using Thing Lindy[_2_] Excel Programming 3 December 3rd 04 07:18 PM
Doing it's own thing André Excel Programming 3 July 9th 04 11:30 PM
Another thing Richard Excel Programming 0 May 21st 04 04:21 PM
oh, and another thing ted daniels Excel Programming 2 April 22nd 04 05:34 PM


All times are GMT +1. The time now is 03:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement