How to write a simple (I think) VB code

Factual Questions
1

I took a Visual Basic class in college 3 years ago so I think what I was to do is possible but I don’t remember how and I can’t find my old book. So here it goes.

I have a spreadsheet with a variety of columns one of which lists the manufacturer by a single initial, B or C. I need to make sure the work is equal between the companies as each person inputs their data into the spreadsheet but I’m unsure of how many lines will eventually be used. So, I think I need to write an if then loop to change the Bs and Cs into numbers to take percentage. I also will need to count the total amount of data imputed so I can do a percentage. Unfortunately, I haven’t been able to remember any more. :smack:

I can do it with an extra column and a fixed number of rows with expressions in the cells but writing a macro is beyond me any more. If you need any more information, please let me know I’d like to get this knocked out before the holidays.

2

No Visual Basic here, but I do know VBScript (used in ASP web pages). Aren’t they closely related? If so, you could use something like the Asc(string) function, which converts the first letter of a string into its ANSI value.


document.write(Asc("W") & "<br />")
document.write(Asc("W3Schools.com"))

Output:

87
87

http://www.w3schools.com/vbscript/func_asc.asp

3

I’ll give that a shot thanks.

4

I’ve done a fair amount of VB, and while this doesn’t seem specially hard, I’m not quite sure I understand what it is you need. Would it be possible to get a screenshot of your spreadsheet (assuming it’s not sensitive information, of course) and explain what you’d like to do from there?

5

You don’t actually say but I assume you are using Excel here.

I have taught a class in VBA for Excel and that would be overkill for what you need.

Your description of the data is incomplete, you only tell us one column with the B or C. You do not say what criteria you are using to measure whether the work is equal between the two–is it the number of rows, or is it some value within the row that must be totaled for each company?

Assuming that the manufacturer ID is in column A, you can place the following formulas anywhere else on the worksheet to tell you how many lines have B and C, respectively:

=COUNTIF(A:A,“B”)
=COUNTIF(A:A,“C”)

If you have another column that has some amount, such as the dollar value of a piece of work, you can similarly add up those values. Suppose that column B contains a value that represents the amount of work for that line (dollars, hours, or whatever). You can sum those values for B and C respectively with the following formulas:

=SUMIF(A:A,“B”,B:B)
=SUMIF(A:A,“C”,B:B)

All of these formulas work regardless of how many rows you add or remove.

Once you can count the number of lines for each manufacturer, and add up some value for each one, then you can do whatever other arithmetic you need, such as percentages, by referring to the cells containing these formulas.

Let me know if this is clear enough. I can post an example to my web site if it’s not.

6

Wow that alot easier then I was planning on. Ya I’m working in Excel. Actually to make sure the work is evenly distributed were just looking for percent of jobs given. Well if i can just do it within a cell that will make my life easier. Thanks for the help.