Looping two criteria - Beginner help

Hi all,

I got the script below from another thread, tested it and it works fine. There are quite a few changes i would like to make to end to meet my ends and as usual i figured someone here could help.

In the long run i want the script to run through a list of customer numbers, check the 'Statement ID' for that customer, append the days transactions to the customer statement sheet.

But one step at a time....

To begin, the script below runs through a list of customer numbers on sheet "sales" and for those that match cell "L19" in that sheet willl copy and paste to a sheet called "Summary"



Sub test2()
Dim SHTNM As String
Dim R As Long
R = 1


SHTNM = "sales"
Sheets(SHTNM).Select
With Sheets(SHTNM).Range("b1", Range("b65536").End(xlUp).Address)

Set c = .Find(Range("l19"), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Sheets(SHTNM).Range(c.Address).EntireRow.Copy
Sheets("Summary").Cells(R, 1).PasteSpecial xlPasteAll
R = R + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


Sheets("Summary").Select
End Sub


At the moment the only criteria is "Cus Number" or "Cell L19". Could someone please help with two criteria e.g for matching "L19" and todays date or date defined in cell "L20"?

I'm not sure if this is a loop but i know i'll have to use that soon....

THanks again for all the help.

Chris

I don't know if this is does what you want.
Replace everything between Do and Loop.... with this:

Please Login or Register  to view this content.

This assumes the date column is 2 columns to the right of the customer no. column. Adjust the number 2 in c.Offset(0,2) as necessary.
The If statement checks the date against cell L20 and the commented out If statement checks against the current date.

Thanks T-J,

Only got around to trying it today though! I had a problem though when running the script. I got a compile error "Loop without Do" on the "Loop While" line:

Sub aaa()
Dim SHTNM As String
Dim R As Long
R = 1


SHTNM = "sales"
Sheets(SHTNM).Select
With Sheets(SHTNM).Range("b1", Range("b65536").End(xlUp).Address)

Set c = .Find(Range("l19"), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If Range("L20").Value = c.Offset(0, 2) Then 'date is offset by +2 from the Cust no. column
If Format(Date, "short date") = c.Offset(0, 2) Then 'date is offset by +2 from the Cust no. column
Sheets(SHTNM).Range(c.Address).EntireRow.Copy
Sheets("Summary").Cells(R, 1).PasteSpecial xlPasteAll
End If
R = R + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Am i also right in thinking that if the date is one column to the left of the name then the offset is (1,0)??

Many thanks,

Chris

Hi,

No ...
offset(0,1)

HTH
Carim

Thanks Carim.

What about the compile error i get, any ideas?

regards,

chris

Hi,

You are missing a second End If just before End With ...

HTH
Carim

Hi,

You are missing a second End If just before End With ...

HTH
Carim

Added the end if just before end with but i still get the "Loop without Do" compile error

Oops ...
Sorry Chris ...
There is One End IF missing before the end of Loop ...

Please Login or Register  to view this content.

HTH
Carim

Thanks Carim that seemed to do the trick. However , i ran the script 12mins ago and it's still going! There are only 50 or 60 lines of data. I'll have to leave it for this evening but i'll keep you posted.

Chris

Chris,

I have not checked your code ...
I have just looked at the coding structure ...

Carim

ah - i see. I'm just trying through trial and error at the moment. What i really need is a place with lots and lots of downloadable examples. Here was one i found quite handy to get started.
http://www.exceltip.com/st/Using_Loo...Excel/628.html

Chris,

First question is about range ("L20")
which is trapped in your loop ...
which means there is no loop ...

Does it help ...

Carim

Originally Posted by T-J

I don't know if this is does what you want.
Replace everything between Do and Loop.... with this:

Please Login or Register  to view this content.

This assumes the date column is 2 columns to the right of the customer no. column. Adjust the number 2 in c.Offset(0,2) as necessary.
The If statement checks the date against cell L20 and the commented out If statement checks against the current date.

Please read the above carefully again. The line was commented out on purpose! As it stands, the code checks against a customer # in L19 and a date in L20:

Originally Posted by chris100

Could someone please help with two criteria e.g for matching "L19" and todays date or date defined in cell "L20"?

To check against a customer # in L19 and today's date, uncomment the second If and comment the first If in the code block above.

I hope this explains it.

Thanks T-J. I was just being v.thick. That definately solved the problem.

Cheers,

Chris

Thanks again for all the help.

I'd now like to extend this a bit further.

How can i alter the script to list through a list of customers, rather than one specific customer. i.e rather than search on "L19", loop through "L1:10" or until blank.

I would print the results at the end of each paste run.

Could someone help on how to take the script this nex step?

Thanks,


Chris