loop, vba

[buttonmaker9]

Hello-
I have a spreed sheet that i recieve daily with 8,000 rows off data 3 columns


Column A is number value 9 digits long all unque values
Column B is 3 charachter text value 30 possible values


I send an email to 30 different location depending on what 3 character code is in column B
I sort the list and select all the once with the same value and copy and paste them in a email with some templated veribaige 30 times until i get to bottom of the list. The number of what each location gets from column A changes daily.


The email that they are sent to is a combination of the B column and email.com. So B2-B20 would be sent to
B2+email.com, second email maybe B21-b67 would go to B21+email.com. and so on til 8000. list size changes daily




So it would be aloop of some type...


VBA 2010
Thanks in advance

[SamT]

The easiest way is to have a list,(an array, or on a sheet,) of the 30 different 3-char codes.
Loop thru the list, Filter the sheet according to the list item then do the rest.

What are the 30 Character Strings?

The other very easy code will require that the number of Rows of data does not change and the number of Rows each email gets does not change. I do not recommend this method.

[JKwan]

Another idea is attach a sample file

[buttonmaker9]

the file looks like this after i remove un needed columns
A


joc
dist


6787727513
KNL


1677172454
KNL


4774562515
KNL


9771727513
COP


6799927513
SFD


6571456513
PHI


8771757545
LAB


6771457513
PDR


6771887513
PDR


9674527513
MKD


6674457513
MKD


8674567513
KNL


6678947513
KNL

[buttonmaker9]

Thanks!

The rows do change. Correction 30; 3 charachter strings; lll, kjk, rfh, etc.,

How would i accomplihed what you speak of...making it cycle thru the array.
I attempted to make it a table but was at alost to make it cycle throught the filter choices.
Hence i thought loop until it change then use new 3 letter code to create email and determin what to pick out of a sorted list.

[SamT]

First: Sort Data by "Dist"


Not real code, just the bare idea

For each 3char in EmailList

TopRow = Range("Dist").Find 3char, After:= Cell(Rows.Count, "Dist", Search direction =next).Row
bottomRow = Range("Dist").Find 3char, After:= Cell(1, "Dist", Search direction = previous).Row

Set RangetoEmail = Range(TopRow & ":" & Bottomrow)

Send(RangetoEmail) to 3char & "@mail.com"
Next

[SamT]

@ JKwan,

Ain't it intrestin' that people ask the experts How-To, but when the experts need more info, the people suddenly know more than the experts and only provide the info they think the experts need?

[buttonmaker9]

SamT

am i guilty of what you speak of? please blame it on ignorance of proper forum ediquette and attempting to ensure my jumble of a question to ye old tested tried and tue coders is somewhat understandable

I appreciate you guys for the direction

[SamT]

Post #2
What are the 30 Character Strings?
Post #3
Another idea is attach a sample file

[JKwan]

I totally agree, Sam. If people don't supply the proper info, what that means to me is that either they don't care or they solved it, therefore, I don't bother myself anymore. I am one of those people that need to have the data. I don't want to imagine what the data look like, nor do I want to create it when the person has it already. Fine, if data is sensitive, well scrub it with rubbish.

[SamT]

I think it just means that they think that if they were as experienced as us, they would not need the information they did not provide us.

I don't get upset with them until after I ask three times or more without any success at all.

I think Buttonmaker will be back with at least an attached example.