Solved

VB Script in Excel

Posted on 2004-07-30
9
234 Views
Last Modified: 2010-05-02
I am working on a project in Excel that I have to take an order status report and be able to take the raw data in and be able to let the salesperson be able to filter out their own name and what their status is. I have the filtering part for the status taken care of. The only problem that I am running across is being able to populate a column from the header to the footer of that section. They are broken down from a status 1-12 and there are 4 different types. Below is an example of what I am trying to accomplish at this point. The "Type" column is what I need to populate

Order #         Client                     Sales Rep                   Type
Status 1 - Entering Order
1234              Brad Smith             Robert B.
1234              Randy Drake           Robert B.
1234              Jessi Abbott            Joyce H.
1234              Jason Leidig            Kristen C.
Total Status 1: 4

This is what I need it to do when the Macro is ran

Order #         Client                     Sales Rep                   Type
Status 1 - Entering Order
1234              Brad Smith             Robert B.                     1O
1234              Randy Drake           Robert B.                     1O
1234              Jessi Abbott            Joyce H.                      1O
1234              Jason Leidig            Kristen C.                    1O
Total Status 1: 4

And so on for the rest of the statuses
0
Comment
Question by:78smithb
  • 4
  • 3
9 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 11685948
You will need to describe how one knows the Type and Status. Most likely, some type of VLOOKUP formula can be used, looking up the information on the raw data sheet.

If the Order number is unique, then you could use a formula like:
=VLOOKUP(A1,'Raw data'!$A$1:$F$500,4,False)
This formula takes the order number (from cell A1) and uses it to look up in a data table in Raw Data worksheet. Column A of Raw data contains the order numbers, and column D contains the Type (the third parameter 4 specifies the fourth column in the data table). The False means the data hasn't been sorted, and an exact match is required for order number. This formula could be pasted on your worksheet using a VBA macro.
0
 
LVL 80

Expert Comment

by:byundt
ID: 11955494
modulo,
78smithb didn't respond to my post--so I object to a delete and refund.

I don't mind a PAQ and refund--but 78smithb should post the solution that was used to solve the problem.
Brad
0
 

Author Comment

by:78smithb
ID: 11956061
To be honest, the only solution I can give was that the project was taken over by someone else who wrote a VB script that was way over my head. He came in and wrote this the Wednesday after I posted my question, so I was kinda out of the picture on this and did not need to pursue it any longer. To be even more honest, I completely forgot to try to close this question after I was out of the picture on this. I really didn't have a chance to get into byundt's response to try to see if I could come to a solution. Again, this was taken over by a co-worker who wrote a macros. I looked at it, and he went a complety different route that was WAY over my head. I think I started in over my head to begin with. Not being a programer,  byundt's response was even too deep for me. I was just somewhat of an assistant on the project and thought I would try Experts-Exchange to get some help to offer my co-worker. That was my plan with byundt's response, but I was never given the chance to do so. Again, I'm sorry, byundt, for not responding this back to you, so if you need to object to a refund, I will just need to learn the hard way. I'm new to all of this and am learning all the ropes of Experts-Exchange
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 80

Expert Comment

by:byundt
ID: 11960295
That's a very reasonable explanation 78smithb.

Could you impose on the your co-worker to give you the snippet of the VB script that addressed your problem? Posting it (or even a description of the approach taken) would tie up the loose ends nicely, and either I or modulo would be glad to refund your points.
Brad
0
 

Accepted Solution

by:
78smithb earned 0 total points
ID: 11962482
This was one of the sections where he was able to take a "type" code that was only on headers from that data spooled into Excel, and place them on each individual line. This was needed in order for us to filter repair orders by "type." I blieve this same general idea was used to take things from headers and place them on al the individual lines.

Dim strType As String

Range("G1").Select

Do

 If ActiveCell.Value = "e A" Then
   strType = "A"
  ElseIf ActiveCell.Value = "e H" Then
   strType = "H"
  ElseIf ActiveCell.Value = "e I" Then
   strType = "I"
  ElseIf ActiveCell.Value = "e N" Then
   strType = "N"
  ElseIf ActiveCell.Value = "e O" Then
   strType = "O"
  ElseIf ActiveCell.Value = "e P" Then
   strType = "P"
  ElseIf ActiveCell.Value = "e R" Then
   strType = "R"
  ElseIf ActiveCell.Value = "e S" Then
   strType = "S"
 End If

If ActiveCell.Value = "A" Or ActiveCell.Value = "H" Or ActiveCell.Value = "I" Or ActiveCell.Value = "N" _
                          Or ActiveCell.Value = "O" Or ActiveCell.Value = "P" Or ActiveCell.Value = "R" _
                          Or ActiveCell.Value = "S" Then
 ActiveCell.Offset(0, 7).Value = ""
Else
 ActiveCell.Offset(0, 7).Value = strType
End If

 ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -6))


Here's one where users were identified with the teams they were on:

Dim intTeam As Integer

Range("F1").Select

Do

If ActiveCell.Value = "78ROSSD" Or ActiveCell.Value = "78BUSBYR" Or ActiveCell.Value = "78WYMACK" _
                                Or ActiveCell.Value = "78DANIEA" Then
  intTeam = "1"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78MORALD" Or ActiveCell.Value = "78VILLAJ" Or ActiveCell.Value = "78SIMSB" _
                                      Or ActiveCell.Value = "78ROBINB" Then
  intTeam = "2"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78MCCULS" Or ActiveCell.Value = "78HOLLIK" Or ActiveCell.Value = "78CERDAC" _
                                      Or ActiveCell.Value = "78STOLSD" Then
  intTeam = "3"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78CASTEK" Or ActiveCell.Value = "78ROGERP" Or ActiveCell.Value = "78LAMBN" _
                                      Or ActiveCell.Value = "78ZERWIR" Or ActiveCell.Value = "78GARCIF" Then
  intTeam = "4"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78NEEDHD" Or ActiveCell.Value = "78HOPKIT" Or ActiveCell.Value = "78MCDONT" _
                                      Or ActiveCell.Value = "78THOMAC" Then
  intTeam = "5"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78ROSASL" Or ActiveCell.Value = "78RIEGEL" Or ActiveCell.Value = "78BRYSOR" _
                                      Or ActiveCell.Value = "78WOODJ" Then
  intTeam = "6"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78DAVISC" Or ActiveCell.Value = "78CLIFTA" Or ActiveCell.Value = "ROSSS" _
                                      Or ActiveCell.Value = "78OCAMPO" Then
  intTeam = "7"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78PHILLT" Or ActiveCell.Value = "78HICKSL" Or ActiveCell.Value = "78ANDERV" _
                                   Or ActiveCell.Value = "78KEMPJ" Or ActiveCell.Value = "78WASHIM" Then
  intTeam = "9"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78PITTS" Or ActiveCell.Value = "78TINDEJ" Or ActiveCell.Value = "78POLKC" _
                                     Or ActiveCell.Value = "78THORNS" Or ActiveCell.Value = "78GUERRB" _
                                     Or ActiveCell.Value = "78GUERRB" Or ActiveCell.Value = "78PEEKJ" _
                                     Or ActiveCell.Value = "78HARVER" Or ActiveCell.Value = "78BEHNKD" Then
  intTeam = "8"
  ActiveCell.Offset(0, 10).Value = intTeam
 ElseIf ActiveCell.Value = "78PITTS" Or ActiveCell.Value = "78MARSHD" Or ActiveCell.Value = "78TURNEB" _
                                     Or ActiveCell.Value = "78STEWAT" Or ActiveCell.Value = "78JONESS" _
                                     Or ActiveCell.Value = "78ABBOTJ" Or ActiveCell.Value = "78ROBERK" _
                                     Or ActiveCell.Value = "78DENNIC" Then
  intTeam = "10"
  ActiveCell.Offset(0, 10).Value = intTeam
 Else
  intTeam = "11"
  ActiveCell.Offset(0, 10).Value = intTeam
End If

 ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -5))
0
 
LVL 80

Expert Comment

by:byundt
ID: 11962650
78smithb,
Thanks for playing ball with the EE "system". I've refunded your points (300) and PAQ the question.
byundt--Excel TA Page Editor
0
 

Author Comment

by:78smithb
ID: 11962693
Thank You for your understanding and patience. I read your member profile. It's hard to believe I've corresponded with a man of your credentials.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now