Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# VB Script in Excel

Posted on 2004-07-30
Medium Priority
248 Views
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              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
Question by:78smithb
• 4
• 3

LVL 81

Expert Comment

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 81

Expert Comment

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.
0

Author Comment

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

LVL 81

Expert Comment

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.
0

Accepted Solution

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 81

Expert Comment

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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll