Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How do I Loop through this range and . . .
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How do I Loop through this range and . . .
My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

Thank you
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 747
Default How do I Loop through this range and . . .
If I understood you correctly then this should do. No looping or copy/paste
actually required. Minimal testing. Ensure you have a copy of your data
before testing:-

Sub GetData()
Dim r As Range, c As Range

Set r = Range(Range("B4"), Range("B4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("D4").Resize(r.Count).Value = r.Value
End If

Set r = Range(Range("C4"), Range("C4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("E4").Resize(r.Count).Value = r.Value
End If

End Sub

Regards,
Greg

"ManKind" wrote:

My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

Thank you
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How do I Loop through this range and . . .
Hi Greg. Unfotunately, I get a run time error 91 Object vairiable or With
Block variable not set when I run this.

Do you know what this refers to?

Regards

AJ

"Greg Wilson" wrote:

If I understood you correctly then this should do. No looping or copy/paste
actually required. Minimal testing. Ensure you have a copy of your data
before testing:-

Sub GetData()
Dim r As Range, c As Range

Set r = Range(Range("B4"), Range("B4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("D4").Resize(r.Count).Value = r.Value
End If

Set r = Range(Range("C4"), Range("C4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("E4").Resize(r.Count).Value = r.Value
End If

End Sub

Regards,
Greg

"ManKind" wrote:

My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

Thank you
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 747
Default How do I Loop through this range and . . .
Sorry, change "N/A" to "NA". If NA is sometimes lower case or there is
potentially other text in the same cell then it can be accomodated. I could
have used a loop and copy/paste but this is a batch process and is lightning
fast if you can use it.

Greg

"ManKind" wrote:

Hi Greg. Unfotunately, I get a run time error 91 Object vairiable or With
Block variable not set when I run this.

Do you know what this refers to?

Regards

AJ

"Greg Wilson" wrote:

If I understood you correctly then this should do. No looping or copy/paste
actually required. Minimal testing. Ensure you have a copy of your data
before testing:-

Sub GetData()
Dim r As Range, c As Range

Set r = Range(Range("B4"), Range("B4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("D4").Resize(r.Count).Value = r.Value
End If

Set r = Range(Range("C4"), Range("C4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("E4").Resize(r.Count).Value = r.Value
End If

End Sub

Regards,
Greg

"ManKind" wrote:

My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

Thank you
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default How do I Loop through this range and . . .
You're a Genius! I had already changed the N/A to NA, however, I was
applying the macro to Sheet 1 which is a linked sheet so as to protect my
original data sheet. That was not working. I just applied the Macro to my
original sheet and it came up trumps!!!! Thank you so Much!!!!

"Greg Wilson" wrote:

Sorry, change "N/A" to "NA". If NA is sometimes lower case or there is
potentially other text in the same cell then it can be accomodated. I could
have used a loop and copy/paste but this is a batch process and is lightning
fast if you can use it.

Greg

"ManKind" wrote:

Hi Greg. Unfotunately, I get a run time error 91 Object vairiable or With
Block variable not set when I run this.

Do you know what this refers to?

Regards

AJ

"Greg Wilson" wrote:

If I understood you correctly then this should do. No looping or copy/paste
actually required. Minimal testing. Ensure you have a copy of your data
before testing:-

Sub GetData()
Dim r As Range, c As Range

Set r = Range(Range("B4"), Range("B4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("D4").Resize(r.Count).Value = r.Value
End If

Set r = Range(Range("C4"), Range("C4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("E4").Resize(r.Count).Value = r.Value
End If

End Sub

Regards,
Greg

"ManKind" wrote:

My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

Thank you
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 747
Default How do I Loop through this range and . . .
Note that there are two instances of N/A to correct. The code already
tolerates lower case "na" contrary to what I just implied. I can easily write
a loop but if there are a lot of data and the presence of NA is reliable then
this should be better.

Greg

"ManKind" wrote:

Hi Greg. Unfotunately, I get a run time error 91 Object vairiable or With
Block variable not set when I run this.

Do you know what this refers to?

Regards

AJ

"Greg Wilson" wrote:

If I understood you correctly then this should do. No looping or copy/paste
actually required. Minimal testing. Ensure you have a copy of your data
before testing:-

Sub GetData()
Dim r As Range, c As Range

Set r = Range(Range("B4"), Range("B4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("D4").Resize(r.Count).Value = r.Value
End If

Set r = Range(Range("C4"), Range("C4").End(xlDown))
Set c = r.Find("N/A", SearchDirection:=xlPrevious)(2)
If Not c Is Nothing Then
Set r = Range(c, c.End(xlDown))
Range("E4").Resize(r.Count).Value = r.Value
End If

End Sub

Regards,
Greg

"ManKind" wrote:

My Data looks like this

NATIONAL - UNITS (000'S)

PRODUCT X PRODUCT Y
23/01/2005 NA NA
30/01/2005 NA NA
06/02/2005 NA 3.064
13/02/2005 NA 48.569
20/02/2005 NA 32.327
27/02/2005 NA 25.774
06/03/2005 NA 20.959
13/03/2005 NA 26.398
20/03/2005 NA 28.601
27/03/2005 NA 14.396
03/04/2005 NA 17.528
10/04/2005 NA 9.500
17/04/2005 NA 12.492
24/04/2005 NA 19.240
01/05/2005 NA 11.613
08/05/2005 NA 22.666
15/05/2005 NA 9.687
22/05/2005 NA 21.081
29/05/2005 NA 21.772
05/06/2005 NA 18.404
12/06/2005 NA 23.086
19/06/2005 NA 22.646
26/06/2005 NA 14.833
03/07/2005 NA 25.108
10/07/2005 NA 22.407
17/07/2005 NA 17.191
24/07/2005 NA 26.532
31/07/2005 NA 14.148
07/08/2005 NA 18.019
14/08/2005 NA 12.632
21/08/2005 NA 22.847
28/08/2005 NA 8.569
04/09/2005 NA 10.303
11/09/2005 NA 10.660
18/09/2005 NA 27.237
25/09/2005 NA 14.033
02/10/2005 0.359 7.881
09/10/2005 12.211 8.879
16/10/2005 20.679 12.699
23/10/2005 30.182 10.520

I need to have a macro that loops through both columns (data starts from
cell B4 for prod x and C4 for Prod y). It needs to locate the first
occurrence of data for column B, copy that cell and paste it in cell D4,
then copy the rest of the values below that point. It then needs to do the
same for Column C and paste the results in Column E starting from cell E4.
ie. I need to do a side by side comparison of how sales for both products
went regardless of the week that the item started selling. I am a VBA virgin
and would apreciate any assistance.

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

Advanced Search
Display Modes
Hybrid Mode Hybrid 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
How to Loop Through This Range and... Faye[_2_] Excel Programming 3 May 3rd 06 03:29 PM
Range Loop lbargers Excel Programming 2 March 28th 06 04:15 PM
Add a Range to an existing Range in a loop? Mick Excel Programming 3 June 18th 05 06:12 AM
loop in range Aksel Børve Excel Programming 3 March 9th 05 02:30 PM
Loop in a Range aksel børve Excel Programming 2 March 8th 05 06:40 PM


All times are GMT +1. The time now is 12:17 AM.

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