Excel VBA Automation Issue

[newguy86]

I'm trying to automate a form that was built in Excel so that the information within the form is automatically organized into a table on another sheet.

I've worked out most of the process but the issue I'm having is that a part of the form lists different codes for each of the broadcast zones that are involved. And along with those zones they include the market area that the zone belongs to. When I compile the information to create the new record for the table they want the information to be grouped by market.

In other words, for each market listed on the form they want a separate line in the table, keeping all the other data the same.

Here's an example list of markets that I'm using to build this process:

Florence-Myrtle Beach
Florence-Myrtle Beach
Los Angeles
Greensboro-H.Point-W.Salem
Greensboro-H.Point-W.Salem
Florence-Myrtle Beach
Los Angeles
Green Bay-Appleton
Dallas-Ft. Worth
Los Angeles
Florence-Myrtle Beach
Green Bay-Appleton
Green Bay-Appleton
Dallas-Ft. Worth
Dallas-Ft. Worth

I figure that I'll need to put together some sort of a Do...Loop statement to loop through the different markets in the form but what's the best way to compile a unique list of markets using VBA that I can then use for the Do...Loop statement?

So far I've been looking at different types of arrays but I haven't quite found anything that will work for what I need.

Any assistance provided will be greatly appreciated!


Travis
Charter Media

 

[SkipVought]

Hi,

What's the logic to apply to the list you provided?

In addition you might also post what you expect your result to appear, based on the given list.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[newguy86]

Hey Skip,
I'm not sure if I understand your question but the list that I provided is generated by the end user when they enter the different zone codes, or syscodes, that apply to the change being requested. There are several syscodes within a single market so each market will usually show up multiple times on the form which also means that every time they fill out this form the list will be different, both in the zones/markets listed and in the overall size of the list.

In the end, what I need is to define some variable/array through VBA that contains a list of markets without blanks or duplicate values. Somewhere down the road I'll need to group together the different syscodes for each market that appears on the form, but that's for a later time. This is the important bit that I need right now in order to make the whole automation thing work.

So in other words, using the info I provided earlier, I simply need a list of markets that I can reference through VBA and it should look something like this:

Florence-Myrtle Beach
Los Angeles
Greensboro-H.Point-W.Salem
Green Bay-Appleton
Dallas-Ft. Worth

That way I can generate a new line in the table I'm compiling for every single market that shows up on the form.

Now if by "What's the logic to apply...", you're asking how my code will be structured, I currently envision it looking something like this:

Sub AutoUpload()

Dim strLabel
Dim strCollectionName
Dim strCollectionType
Dim strDesc
Dim strNetworkAdd
Dim strNetworkRemove
Dim strSysCode
Dim arrMarket as Variant
Dim Count
Dim checkVal

Count = 2

'Defining the static data that will be used in all records.
strLabel = Range("B8").Value
strCollectionName = Range("B8").Value
strColletionType = "Orbit"
strDesc = Range("G8").Value

'An SQL string that groups together all the nets that are marked as 'Add'. 
'That data can be found within the following cell ranges: C12:C64, H12:H64, M12:M64.
strNetworkAdd = 

'An SQL string that groups together all the nets that are marked as 'Remove'. 
'That data can be found within the following cell ranges: D12:D64, I12:I64, N12:N64.
strNetworkRemove = 

'This is where the list of markets will be defined. 
'That data is located between the cells of R12:R43.
arrMarket = 

'Clear out old data
Sheets("Orbit Upload").Select
checkVal = Range("A2").Value
Do Until checkVal is null
[indent]If checkVal is not null Then[/indent]
[indent][indent]Rows("2:2").Select[/indent][/indent]
[indent][indent]Selection.Delete Shift:=xlUp[/indent][/indent]
[indent][indent]checkVal = Range("A2").Value[/indent][/indent]
[indent]End If[/indent]
Loop

'Insert new data into table
Sheets("Orbit Upload").Select
For each item in arrMarket

[indent]'An SQL string that groups together all the syscodes listed within the form that correspond to the current market being compiled.[/indent]
[indent]'This data can be found within the cells of P12:P43.[/indent]
[indent]strSysCode = [/indent]
[indent]Range("A" & Count).Value = strLabel[/indent]
[indent]Range("B" & Count).Value = strCollectionName[/indent]
[indent]Range("C" & Count).Value = strCollectionType[/indent]
[indent]Range("D" & Count).Value = strNetworkAdd[/indent]
[indent]Range("E" & Count).Value = arrMarket[/indent]
[indent]Range("F" & Count).Value = strDesc[/indent]
[indent]Range("G" & Count).Value = strSysCode[/indent]
[indent]Count = Count + 1[/indent]
Next

MsgBox "Data is ready for upload."

End Sub

I hope this answers your questions. Please feel free to let me know if you have any others.

And any assistance you guys can provide will be greatly appreciated.

Travis
Charter Media

 

[SkipVought]

How about...
[tt]
Data > Data tools > Remove duplicates
[/tt]

Here's how I would do it.

Create a new sheet for reference data. I usually call mine Factors.

Copy the list that your users provide and paste into your new reference sheet under a heading like Markets

SELECT the entire list and heading

Data > Data tools > Remove duplicates

SELECT again

Formulas > Define names > Create from selection > Create from names in the TOP row

Now the Named Range, Markets, can be used in your code or on any sheet to reference that list.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]