Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with Loop Please
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Help with Loop Please
Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up to
column G) wide, and cover as many rows down


Thanks, Don




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Help with Loop Please
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up to
column G) wide, and cover as many rows down


Thanks, Don





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 735
Default Help with Loop Please
Why not just copy the data to the archive.....

Replace all of this in your (my!) code

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

with this code.....

With wbOriginal.Sheets("Data")
.Range("A3:G" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy _
Destination:=wbArchive.Sheets("Sheet1").Range("A1" )
End With

--

Regards,
Nigel




"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
KC KC is offline
external usenet poster
  
Posts: 55
Default Help with Loop Please
Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
..Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between 10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Help with Loop Please
Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Help with Loop Please
Thank you KC...........I think this will do the trick
"KC" wrote in message
...
Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
.Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Help with Loop Please
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
totRows = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
totCols = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

For lngRow = 3 To totRows
For lngCol = 1 To totCols
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With



--
If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don








Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default Help with Loop Please
I mean.........

With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
totRows = .Cells(Rows.Count, "A").End(xlUp).Row
totCols = .Cells(1, Columns.Count).End(xlToLeft).Column

For lngRow = 3 To totRows
For lngCol = 1 To totCols
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next

End With


If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Thanks Jacob, but I never know how many rows I'll have...........but I do
believe KC hit it on the head......
"Jacob Skaria" wrote in message
...
With wbOriginal.Sheets("Data")
'Loop from 3rd row to 100 row/Col A(1) to G(7)
For lngRow = 3 To 100
For lngCol = 1 To 7
wbArchive.Sheets("Sheet1").Cells(lngRow - 2, lngCol) = .Cells(lngRow,
lngCol).Value
Next
Next
End With

If this post helps click Yes
---------------
Jacob Skaria


"DonW" wrote:

Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Help with Loop Please
Sorry I'm getting back so late........thanks KC....your's worked the best
for me......and yes, there was a typo in my post......

Don
"KC" wrote in message
...
Is there a typo that the line before End With should have B2 instead of B1
please?
if affirmative, may be

With wbOriginal.Sheets("Data")
n=.range(.range("a3"),.range("a3").end(xldown)).ro ws.count
wbArchive.Sheets("Sheet1").Range("A1").resize(n,7) =
.Range("A3").resize(n,7)
End With


"DonW" wrote in message
...
Does anyone know how I can set up a loop so (let's say anywhere between
10
to 150 or more rows) "all" of the data could be accounted for?

This is what I have:

Dim wbArchive As Workbook
Dim wbOriginal As Workbook

Set wbArchive = ActiveWorkbook
wbArchive.SaveAs Filename:="E:\Archived Logs\Data.xls"

Windows("original_file.xls").Activate
Set wbOriginal = ActiveWorkbook

With wbOriginal.Sheets("Data")
wbArchive.Sheets("Sheet1").Range("A1") = .Range("A3").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B3").Value
wbArchive.Sheets("Sheet1").Range("A2") = .Range("A4").Value
wbArchive.Sheets("Sheet1").Range("B1") = .Range("B4").Value
End With

the wbOriginal header row starts in row 3.
I'd like to be able to loop through the rest of the wboriginal so all (up
to
column G) wide, and cover as many rows down


Thanks, Don








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
returning back to loop check condition without completing the loop ashish128 Excel Programming 13 April 3rd 08 12:53 PM
Loop to Filter, Name Sheets. If Blank, Exit Loop ryguy7272 Excel Programming 3 February 5th 08 03:41 PM
(Complex) Loop within loop to create worksheets klysell Excel Programming 1 March 20th 07 12:03 AM
Advancing outer Loop Based on criteria of inner loop ExcelMonkey Excel Programming 1 August 15th 05 05:23 PM
Problem adding charts using Do-Loop Until loop Chris Bromley[_2_] Excel Programming 2 May 23rd 05 01:31 PM


All times are GMT +1. The time now is 12:10 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement