Need Help With this code

[Shawnathon]

I am needing help with this code.

This is what is in the cell - balance: $0.00 ($10.65 pending)

I need to take the balance amount & the pending amount and separate them with just the $ figure, no "balance" & "pending"

Like this:
$0.00
$10.65

With the balance being on top of the pending

I will need the code to work if the $ amounts are different, some are in the hundreds and some are like this.

Thanks in advance.
Shawn

 

[Rick Rothstein]

You didn't tell us anything about your setup, so I made this code work against the ActiveCell...

Sub SplitBalancePendingCell()
  Dim Money() As String
  Money = Split(ActiveCell.Value, "$")
  ActiveCell.Value = Format(Val(Money(1)), "$0.00") & vbLf & Format(Val(Money(2)), "$0.00")
End Sub

 

[Shawnathon]

Hey thanks Rick, that worked well, the only thing is I need it to go in separate rows on top of each other, sorry for not being so clear. Thanks!

I am pretty new to setting up macros, So what if I wanted to point it to convert a certain cell (example - A10) instead of the active cell. Thanks

Shawn

 

[Rick Rothstein]

We need some more information. Is this being done to one cell or are there a series of cells involved? If a series, where are they on your worksheet (a specific row or series of rows; a specific column or series of columns; if not the last of data on your sheet, then should rows be inserted to account for the new data or is the data supposed to overwrite whatever is below it, etc.)? The more detail you can give about your layout and what the final result should look like, the better. Remember... we can't see your worksheet's setup and we don't know what you are ultimately trying to accomplish, so you have to tell us this in order for us to be able to the craft code to implement your plans.

 

[Shawnathon]

Hi Rick,

OK, the original worksheet (sheet1) has all the info on it. In cell A10 it has the "balance: $28.95 ($10.43 pending)" Also there is a Total balance of "$404.09" in cell A14. All of the worksheets are the same. There is other info on the the worksheet but this is the only 2 cells I need to work with.

This is what I need the code to do. In sheet2 I would like the Total balance to show up in A1, the balance in B1 and the pending in C1.

There has been some changes made since the first post, sorry about that. this is what I am looking for the code to accomplish. Thanks Rick.

 

[RichardMGreen]

Is the information in cell A10 always in this format:
"balance: $28.95 ($10.43 pending)"

 

[Shawnathon]

Hi RIchard,

Yes the info is always that way. and always in A10

I just need the $ figure and in separate cells.

Let me know if you need more clarification. I have all the info below, thanks!

Shawn

 

[RichardMGreen]

In that case, you shouldn't need code.
Try the following formulas in the necessary cells:-
To get the balance use =MID(B7,FIND(":",B7)+2,LEN(B7)-FIND("(",B7)-9)
To get the pending amount use =MID(B7,FIND("(",B7)+1,LEN(B7)-FIND("(",B7)-9)

I've run a quick test on it and it seems to work. You'll have to adjust the cell references to suit your own specific requirements.
Let me know if you have any problems.

 

[RichardMGreen]

Just realised that the values the formulas will return will be treated as text.
If you want to have actual numeric values that use can sum, etc, try these:-
To get the balance use =MID(B7,FIND(":",B7)+3,LEN(B7)-FIND("(",B7)-9)*1
To get the pending amount use =MID(B7,FIND("(",B7)+2,LEN(B7)-FIND("(",B7)-9)*1

 

[Shawnathon]

Hmmm Richard, you lost me on that one. Not to sure where to place the formula. Here is an explanation of what I need it to do. Info needs to go from sheet 1 to sheet 2.

OK, the original worksheet (sheet1) has all the info on it. In cell A10 it has the "balance: $28.95 ($10.43 pending)" Also there is a Total balance of "$404.09" in cell A14. All of the worksheets are the same. There is other info on the the worksheet but this is the only 2 cells I need to work with.

This is what I need the code to do. In sheet2 I would like the Total balance to show up in A1, the balance in B1 and the pending in C1.

Thanks for your help on this, appreciate it.

Shawn