How do I Loop through this range and . . .

ManKind · 06-04-2006, 02:25 AM

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

Greg Wilson · 06-04-2006, 03:10 AM

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

ManKind · 06-04-2006, 03:25 AM

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

Greg Wilson · 06-04-2006, 03:40 AM

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

ManKind · 06-04-2006, 03:45 AM

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

Greg Wilson · 06-04-2006, 03:45 AM

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