VBA Function to explode string

Hi.

I am wanting to know hbow I would go about getting the following 3
cells:

Cells A1, A2, A3:
::
1-4,6,8 50 S1B1::

Into a form that looks more like this
::A1 A2 A3
1 8.33 S1B1
2 8.33 S1B1
3 8.33 S1B1
4 8.33 S1B1
6 8.33 S1B1
8 8.35 S1B1::

How it does the rounding (and on which one), it does not matter!

Thanks,
Tom


-- 
tomjermy
------------------------------------------------------------------------
tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666
View this thread: http://www.excelforum.com/showthread.php?threadid=382414

0
6/27/2005 9:52:56 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
461 Views

Similar Articles

[PageSpeed] 10

You haven't explained the rules of how you get from what you have to what
you want.

-- 
 HTH

Bob Phillips

"tomjermy" <tomjermy.1ra52a_1119866704.1096@excelforum-nospam.com> wrote in
message news:tomjermy.1ra52a_1119866704.1096@excelforum-nospam.com...
>
> Hi.
>
> I am wanting to know hbow I would go about getting the following 3
> cells:
>
> Cells A1, A2, A3:
> ::
> 1-4,6,8 50 S1B1::
>
> Into a form that looks more like this
> ::A1 A2 A3
> 1 8.33 S1B1
> 2 8.33 S1B1
> 3 8.33 S1B1
> 4 8.33 S1B1
> 6 8.33 S1B1
> 8 8.35 S1B1::
>
> How it does the rounding (and on which one), it does not matter!
>
> Thanks,
> Tom
>
>
> -- 
> tomjermy
> ------------------------------------------------------------------------
> tomjermy's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24666
> View this thread: http://www.excelforum.com/showthread.php?threadid=382414
>


0
phillips1 (803)
6/27/2005 10:36:04 AM
Sorry, I make this clearer:

What I want to do is have cells where users enter data as so:

CELL A1: 2-3,6
CELL B1: 40
CELL C1: S1B1

Users can enter data in this format into columns (ABC), on multiple
rows.

From this data, I would like to write a script which takes the first
line, and explodes CELL A1 into:

CELL D1: 2
CELL D2: 3
CELL D3: 6

Then take the number in CELL B1 ($40) and split that amount over the 3
cells, to 2 decimal places, rounded so that the values equal the $40
entered to B2. This goes into cells E1:E3:

CELL E1: $16.66
CELL E2: $16.66
CELL E3: $16.68

Then take CELL C1 and show this value in CELL F1, F2, F3.

As mentioned at thje beginning, the idea is to allow many entries into
the rows of columns ABC, and perform this proceedure on all of them.

In summary, I want to go from:
2-3,6 40 S1B1
1-4 40 S5J1
to 

2 16.66 S1B1
3 16.66 S1B1
6 16.68 S1B1
1 10 S5J1
2 10 S5J1
3 10 S5J1
4 10 S5J1

If someone can help me with all or even part of this, that would be
great!

Thanks,
Tom


-- 
tomjermy
------------------------------------------------------------------------
tomjermy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24666
View this thread: http://www.excelforum.com/showthread.php?threadid=382414

0
6/28/2005 8:54:05 AM
Reply:

Similar Artilces:

Excel 2003 - VBA
Hi Guys: A quick question on the "Workbook Open" event: When I load up Excel and open the workbook, this event triggers. If I then close the workbook, without closing Excel, then open the workbook, it does not occur. Close down the worksheet and Excel, then open it, it triggers. Is this normal operation? Could it have to do with the possibility the I had set Application.EnableEvents = False, before exiting the workbook. Craig Also put in sub auto_open() -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Craig Brandt" <brandtcraig...

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

Lock and Unlock cells using VBA
Hello All Anyone know how to lock cells using VBA, also unlock others currently locked. Page protection will be in use at the time this needs to be done. Also, is there anywhere on the net with a complete list of VBA functions, including a short statement of what each one does, with or without examples. If not, any good books that cover the above area. I'm really (strange as it might seem), enjoying playing with VBA and seeing exactly what it can do. Regards Peter The easy questions... Excel's Help is a very good source. take a look at Peter Nonely's workbook that describes...

Excel functions
Hi everyone, Hope somebody could help me with these problems. Problem #1. row 1 =DSUM(database,field,'$A4:$A5) where A4 = "destin"; A5 = "Consolidator 1" row 2 =DSUM(database,field,'$A6:$A7) where A6 = "destin"; A7 = "Consolidator 2" ..... row n =DSUM(database,field,'$An:$Am) where An = "destin"; Am = "Consolidator n" I waste a lot of rows for this criteria only because I do not know ho to create it another way, I mean (A4, A6 and so on) Problem #2 I have named several range of cells in workbook. I want to change...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

Populate List Box
Need some ideas. I have a list box from which our clients will be able to select which form they want to preview or print. However, each client should only have selections available that apply specifically to their company. For example: Company A will require forms 1, 2, 3, 6 and 7 Company B will require forms 1, 6 and 7 Company C will require forms 4, 5 and 6 I have some code right now to handle a simple two form variation: If Forms("Main").Controls("Form1Req") = "Yes" and Forms("Main").Controls ("Form2Req") = "No" Then Me.L...

converting hand drawn sketches into Visio Cross Functional Horizon
Hi , I am new to Visio 2003 . Can anybody tell me how can one convert hand drawn sketches into Visio Cross Functional Horizontal diagrams? Thanks Harsha Are you asking about a way to scan images into Visio and transform them into drawings? Or are you asking how to assemble a Cross Functional diagram? To make a Cross Functional diagram, you first choose how many bands to create. Then you click on the headings for the bands to change the text to the names you want. Switch to the Basic Flowchart shapes in the Shapes Window and drag out the Process shapes to create the steps in you...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

XML deserialization from string
Hi, I have an XSD and created a VB.NET class file from it. When I have an XML file (conforming to the schema), it is straight forward to deserialize using the class file. I am looking for ways to deserialize when I have the XML as a string and not in a file. Writing the string to a file and deserializing from the file might have an adverse effect on the performance. Anyone know of a good method to do this? Any help will be appreciated. Thanks, Sampath. Use a StringReader to read the file and wrap that in an XmlTextReader. -- This posting is provided "AS IS" with no warranties,...

Excel super slow on paste function in 2007
When you copy and paste one cell to another I get the circular waiting bar and after 4-5 seconds it pastes. If I'm doing a bunch of cells, it still takes 4-5 seconds per cell and the cells will appear right to left, 1 every 4-5 seconds until it's complete. If I hit ESC, everything copies and pastes right away. This started happening when we converted a 2003 file to 2007. It affects everyone so it's not my PC, it's the document. There are no active add-ons but I recently downloaded an add-in that I got from this discussion board to find hidden links (it's d...

VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same as the printer setup in GP (Files>Print Setup)? eg: is GP is using Printer1, then when calling a crystal report using VBA, the printer will be set to Printer1. If GP is using Printer2, then crystal report will be defaulted to Printer2. thks in advance. Unfortunately that isn't going to work for a couple reason: 1. The biggest reason is there isn't any way to determine what the printer is in Dynamics. Not even with Dexterity. It just wasn't something that was exposed. So I can't think of ...

Automatic Functions in Excel
Hi There I am developing a Time Sheet in Excel for Staff to record times worked on it. I am using the 1904 date system as occasionally a negative total will be displayed. I would like it if someone was on annual leave they would be able to record this by typing in "Annual Leave" or "A/L" or something similar then the total for that day to display as 7:00 (this is in hours). At present the "total" column runs a calculation of the time finished minus the time started. Is this possible? Regards Colin Hi Colin, StartTime in column A, end ti...

is there a baby-sitting function in Exchange
SBS2003 Premium. One user is not very diligent on reading and responding to emails. He is on the road quite a bit and when he gets back he will not remember that one important email that we needed to respond to. Is there a way that I can have other users have his mailbox open as well, so that they can monitor it and make sure things get replied to? There appears to be such functionality in OL2003, but it just doesn't open the mailbox. Plus his password changes every 30 days, as per the administrator settings, how do we deal with that? Thanks in advance, Robert Assign Full Mailbox ...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

wchar_t as a string parameter
I believe I am going crazy. I just compiled the 'release' version of my program and am having issues with the passing of a wchar_t string to a function. Both Debug and Release: - stdcall calling convention - treat wchar_t as built-in type = NO - unicode char set code: bool MyFunction( LPTSTR szString ); wchar_t szMyString[31]; When I declare the szMyString variable, it has a starting memory address 4 bytes BEFORE the address that it has when it is passed inside the function. This only happens on the Release version of the program. Am using VS2005. Any ideas? Thanks, Je...

How to Declare Pointer to Member Function?
I have a thread that needs to make data visible to a function inside a separate class. In the past, I was using PostMessage to get my information to the class function, but occasionally a message gets lost this way. I could declare the class function as static, but then the function would not be able to interact with other parts of the class. The data is thread safe. // header: typedef void (*PtrToLpMsg)(LPTSTR lpMsg); PtrToLpMsg g_AddStatusMsgFn; // code: CMain::CMain() { g_AddStatusMsgFn = AddStatusMsgA; } void CMain::AddStatusMsgA(LPTSTR lpMsg) { // other code } On Mon, 16 ...

non vba way to print non continuous ranges #2
Thanks Myrna, I didn't know about the hide columns facility. It makes what I want to do a lot easier : -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 ...

Function to control how data displayed
I have 2 columns that I exported from Access to Excel. In Access th columns were Yes or No. In excel they display as True or False. I wan them to show as Yes or No what is the function to make this happen? Thank you! -- LOgle531 ----------------------------------------------------------------------- LOgle5318's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2659 View this thread: http://www.excelforum.com/showthread.php?threadid=39860 LOgle5318, Assuming your True and False are in columns A & B then In C1 put =IF(A1="True","Yes") and...

Error message when retriving a string from my xml file in C#.net a
I created the following code in my C# program but it's giving me error message at run time of : XML.XPATH.XPATHEXCEPTION : Namespace Manager or XSLTContext needed. This query has a prefix, variable or user defined function. Can someone see what I'm doing wrong? Thanks, Alpha private XPathDocument unityMessages = new XPathDocument("UnityMessages.xml"); unityMsgNavigator = unityMessages.CreateNavigator(); string query = @"/trans-unit[@id=""m1""]/target[@xml:lang=""fr""]"; XPathExpression queryM1 = unityMsgNavigator.Co...

XL2003: INDIRECT() function changes calling cell
Hello all, It appears that the INDIRECT() function changes the calling cell. Let me try to explain: I have a dynamic named range "NR6.PerMonth": - When "=NR6.PerMonth" is in cell C27 on the "Summary" sheet, it will refer to/return cell C982 on the "6" sheet. - When "=NR6.PerMonth" is in cell D27, it will refer to/return cell D982....and so on and so forth, going across. BUT!!! (A26 on the "Summary" sheet is a formula that returns the number 6...A982 on the "6" sheet is a label): - When '=I...

in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell?
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? Hi Daniel, See http://www.mvps.org/dmcritchie/excel/event.htm change event excel does not keep track of the old value, nor can you get if from the event code. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniel" <softwareengineer98037@yahoo.com> wrote i...

Average Function #2
Hi Everyone, I am using Excel 2003. I understand there is probably a very sophisticated way to get what I want but I need to learn the simple way first. I have a data sheet named "MICU". I inserted another sheet in the workbook called "Stats". I want to break down the data by month on my new Stats sheet. The MICU sheet has many columns of info. The 2 I think I need to use is a column for start date "ThpyStDtTm" (Date and Time field 3/14/09 18:41) and a column called "VentLOSDays" (Number format with 2 decimal places). Column A Column...

How to call a function from another workbook
Dear sir, how can i call a function or procedure that contained in another workbook ? thanks Joe Hi When you want to use an UDF or procedure in several workbooks, then save it into a module of Personal Macro Workbook (Personal.xls, it's created automatically, when you select Personal Macro Workbook as destination to save a newly-created macro, and later is loaded automatically whenever you start Excel). -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "Joe" <Joe@discussions.microsoft.com> wrote in message news:AF3EABAE-6751-4917-88F1-1A5...

Function Call Problem
Could anyone help me with this Dialog App (first visual app). I am writing a test program for using Tab Controls. However, I have found that I am not sure how to call a class member function from the main dialog class to another dialog class. The program compiles normally. But the function call is causing an "Assertion Failed" error message whenever the button is clicked. It is used to print a number in a dialog of a tab control, and it also prints the same number in the main dialog. Both of these numbers are printed in Static Text Controls. This is the code from the main Dial...

count function in pivot table
How to count the number of types under certain group in a pivot table: Such as one sku occurs twice under one customer, I only need 1 in pivot table, instead of count the numbers of occurrences: 2 A pivot table won't calculate a unique count. You could add a column to the list, then add that field to the pivot table. There's an example here: http://www.contextures.com/xlPivot07.html#Unique Holly wrote: > How to count the number of types under certain group in a pivot table: > > Such as one sku occurs twice under one customer, I only need 1 in pivot > table, inst...