How do I Loop through this range and . . .

[ManKind]

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]

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]

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]

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]

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]

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