I don't remember putting the '0' in; maybe my brain was fried that late at night, or maybe my fingers did it all by themselves without my having to think about it all. At any rate, it shouldn't have been in there, so you did right.
Quote:
|
....went into VBA, clicked on 'view code' icon in the Project - VBAProject window and pasted your code.
|
It probably doesn't matter, but I didn't hear you say anything about what module you pasted the code into. Did you create a new one, or use one that was already listed among the "Microsoft Excel Objects" in the Project window? The program worked, and that's probably all that counts, but I usually prefer to create ("insert") a module for each new main program I'm creating. Perhaps someone else here can give good reasons for that, but all I have to defend it is a sense of neatness or organization. Of course, when you're writing
big project it matters, but maybe not for this.
Anyway, let's proceed. I said that your program will eventually read the data directly from the text file, but let's start by writing part of the code that'll turn a text string into the six display columns. I've looked at the two sample strings you supplied, and there are some differences that I need to ask about, but the general pattern seems to be this:
- The item name
- Delimiter '#'
- A series of quantities and their corresponding prices, each delimited (or perhaps terminated) by "\r\n"
- Delimiter '#'
- Text that can be discarded
Each item #3 breaks down like this:
- Lower quantity
- Delimiter '|'
- Higher quantity (ignored during import, calculated during export)
- Delimiter '|'
- Price
Is all that correct? And what's that bit with NO AD and WITH AD? Your program will have to know what to do with that, too.
Once we can define exactly how that string is defined, we can explain it to your program. There are always lots of ways to do any particular task in a programming language, but I would probably go about it like this:
- Use the Split function to cut the main string up into parts delimited by '#'. Let's assume we call this "Array1"; that puts the item name in Array1(0), all the quantities and princes in Array1(1) and the rest (which we're ignoring) into 2 and 3 and beyond.
- Next cut the quantities/price series (currently in Array1(1)) into the various sub-pieces delimited by "\r\n". Let's put those in Array2; the lowest quantities and prices would be in Array2(0), the next in Array2(1) and so on.
- Then cut each piece in Array2 by '|'; the low quantity would be in Array3(0), the high quantity in Array3(1) and the price in Array3(2).
- Put each of the pieces into the worksheet.
The first part of this code might look like this:
Code:
Sub Main()
x = Selection.Value
arr1 = Split(x, "#")
Item = arr1(0)
arr2 = Split(arr1(1), "\r\n")
For jq = 0 To UBound(arr2, 1)
arr3 = Split(arr2(jq), "|")
' Write something here to put the values into the worksheet.
Next jq
End Sub
Now, this isn't ready to run; it cuts up the pieces correctly enough (as far as I know), but it doesn't put anything into spreadsheet where you can see it. I'm stopping here partly so you can look it over and see how much of it you understand (this is where you should ask questions), and partly so you'll have a chance to tell me—it's it's true—"Oh, I get that part, I just don't know how to [something else]".