INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel VBA Automation Issue

Excel VBA Automation Issue

(OP)
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

RE: Excel VBA Automation Issue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA Automation Issue

(OP)
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:

CODE -->

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
If checkVal is not null Then
Rows("2:2").Select
Selection.Delete Shift:=xlUp
checkVal = Range("A2").Value
End If
Loop 'Insert new data into table Sheets("Orbit Upload").Select For each item in arrMarket
'An SQL string that groups together all the syscodes listed within the form that correspond to the current market being compiled.
'This data can be found within the cells of P12:P43.
strSysCode =
Range("A" & Count).Value = strLabel
Range("B" & Count).Value = strCollectionName
Range("C" & Count).Value = strCollectionType
Range("D" & Count).Value = strNetworkAdd
Range("E" & Count).Value = arrMarket
Range("F" & Count).Value = strDesc
Range("G" & Count).Value = strSysCode
Count = Count + 1
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

RE: Excel VBA Automation Issue

How about...

Data > Data tools > Remove duplicates


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close