How-to create macro?
 

Hi,

I would greatly appreciate the assistance of you Excel mavens. Here's what I
would like to accomplish:

1) My rows that have a field that looks like this (no quotes):
€œTaskName, 12+75+42+5+36€�
a. Note that the numbers above (ie. 12+75+42+5+36 in this example)
vary from row to row. For example, the cell in the next row might look like
this (no quotes):
€œTaskSize, 10+22+84€�

2) I would like to create a macro that would look at that cell and then do
the addition on the numbers on the right (ie. 12+75+42+5+36 or 10+22+84),
while ignoring the text on the left (ie. TaskName, ) (ie. Taskname comma
space)

3) I would then like the macro to put the result of the math in a filed that
is 5 columns to the right.

This may be pretty easy, but I am not quite certain how to accomplish the
parsing of the variable amount of numbers and plus signs within the field. My
guess is that somehow Excel can read the cell form right to left, and then
know to stop when it sees the €œ, €œ (ie. comma and a space).

I look forward to your thoughts on this one.

Regards,
Brcobrem

How-to create macro?
 

Select the cells in the column that have this construct and run the macro or
change selection to something like Range("B2:B30")

Sub aaa()
Dim cell As Range, v As Variant
Dim dSum As Double, i As Long
For Each cell In Selection
dSum = 0
v = Split(Replace(cell, "+", ","), ",")
For i = LBound(v) To UBound(v)
If IsNumeric(v(i)) Then
dSum = dSum + CDbl(v(i))
End If
Next
cell.Offset(0, 5).Value = dSum
Next
End Sub

--
Regards,
Tom Ogilvy

"Brcobrem" wrote:

Hi,

I would greatly appreciate the assistance of you Excel mavens. Here's what I
would like to accomplish:

1) My rows that have a field that looks like this (no quotes):
€œTaskName, 12+75+42+5+36€�
a. Note that the numbers above (ie. 12+75+42+5+36 in this example)
vary from row to row. For example, the cell in the next row might look like
this (no quotes):
€œTaskSize, 10+22+84€�

2) I would like to create a macro that would look at that cell and then do
the addition on the numbers on the right (ie. 12+75+42+5+36 or 10+22+84),
while ignoring the text on the left (ie. TaskName, ) (ie. Taskname comma
space)

3) I would then like the macro to put the result of the math in a filed that
is 5 columns to the right.

This may be pretty easy, but I am not quite certain how to accomplish the
parsing of the variable amount of numbers and plus signs within the field. My
guess is that somehow Excel can read the cell form right to left, and then
know to stop when it sees the €œ, €œ (ie. comma and a space).

I look forward to your thoughts on this one.

Regards,
Brcobrem

How-to create macro?
 

Hi Tom,

Thank you for that detailed example. It took me a couple minutes to figure
out what to do wih your code, but I finally figured out how to use the macro
VBA editor. You will get a laugh to know that it took me another 15 minutes
to figure out how to assign a keyboard shortcut to the macro. In the Macro
Options, I pressing Ctrl_Shift_F and it kept displaying only Ctrl_F (of
course the undocumented trick is that Ctrl is already assumed, you only need
to press Shift_F to get Ctrl_Shift_F).

I also hacked a couple changes (see below). I see that I could change the
macro name by replacing your 'aaa' with my 'do_math_F' . I needed this
because I want variations of the macro to place the result in different
columns (in this case column F, which has the offset value of 4). I also
needed the result to display negative, hence the "* -1" addition to your
cell.offset line.

It's been almost 20 years since I've had the occasion to work with macros.
You may remember the early versions of Lotus 123. They used to call me "Graph
Man" back in the early 1980's when I was the only person in the company who
even knew how to use a personal computer (yes, HP did have some back then) .
I used Lotus to plot linerar regression for seasonal inventory anaysis.

Thanks for helping me shake out the cobwebs. I really appreciate your
assistance.

Regards,
Brcobrem

Hackage:
Sub do_f_math()
Dim cell As Range, v As Variant
Dim dSum As Double, i As Long
For Each cell In Selection
dSum = 0
v = Split(Replace(cell, "+", ","), ",")
For i = LBound(v) To UBound(v)
If IsNumeric(v(i)) Then
dSum = dSum + CDbl(v(i))
End If
Next
cell.Offset(0, 4).Value = dSum * -1
Next
End Sub



"Tom Ogilvy" wrote:

Select the cells in the column that have this construct and run the macro or
change selection to something like Range("B2:B30")

Sub aaa()
Dim cell As Range, v As Variant
Dim dSum As Double, i As Long
For Each cell In Selection
dSum = 0
v = Split(Replace(cell, "+", ","), ",")
For i = LBound(v) To UBound(v)
If IsNumeric(v(i)) Then
dSum = dSum + CDbl(v(i))
End If
Next
cell.Offset(0, 5).Value = dSum
Next
End Sub

--
Regards,
Tom Ogilvy

"Brcobrem" wrote:

Hi,

I would greatly appreciate the assistance of you Excel mavens. Here's what I
would like to accomplish:

1) My rows that have a field that looks like this (no quotes):
€œTaskName, 12+75+42+5+36€�
a. Note that the numbers above (ie. 12+75+42+5+36 in this example)
vary from row to row. For example, the cell in the next row might look like
this (no quotes):
€œTaskSize, 10+22+84€�

2) I would like to create a macro that would look at that cell and then do
the addition on the numbers on the right (ie. 12+75+42+5+36 or 10+22+84),
while ignoring the text on the left (ie. TaskName, ) (ie. Taskname comma
space)

3) I would then like the macro to put the result of the math in a filed that
is 5 columns to the right.

This may be pretty easy, but I am not quite certain how to accomplish the
parsing of the variable amount of numbers and plus signs within the field. My
guess is that somehow Excel can read the cell form right to left, and then
know to stop when it sees the €œ, €œ (ie. comma and a space).

I look forward to your thoughts on this one.

Regards,
Brcobrem

How-to create macro?
 

Your very welcome.

--
Regards,
Tom Ogilvy

"Brcobrem" wrote in message
...
Hi Tom,

Thank you for that detailed example. It took me a couple minutes to figure
out what to do wih your code, but I finally figured out how to use the
macro
VBA editor. You will get a laugh to know that it took me another 15
minutes
to figure out how to assign a keyboard shortcut to the macro. In the Macro
Options, I pressing Ctrl_Shift_F and it kept displaying only Ctrl_F (of
course the undocumented trick is that Ctrl is already assumed, you only
need
to press Shift_F to get Ctrl_Shift_F).

I also hacked a couple changes (see below). I see that I could change the
macro name by replacing your 'aaa' with my 'do_math_F' . I needed this
because I want variations of the macro to place the result in different
columns (in this case column F, which has the offset value of 4). I also
needed the result to display negative, hence the "* -1" addition to your
cell.offset line.

It's been almost 20 years since I've had the occasion to work with macros.
You may remember the early versions of Lotus 123. They used to call me
"Graph
Man" back in the early 1980's when I was the only person in the company
who
even knew how to use a personal computer (yes, HP did have some back then)
.
I used Lotus to plot linerar regression for seasonal inventory anaysis.

Thanks for helping me shake out the cobwebs. I really appreciate your
assistance.

Regards,
Brcobrem

Hackage:
Sub do_f_math()
Dim cell As Range, v As Variant
Dim dSum As Double, i As Long
For Each cell In Selection
dSum = 0
v = Split(Replace(cell, "+", ","), ",")
For i = LBound(v) To UBound(v)
If IsNumeric(v(i)) Then
dSum = dSum + CDbl(v(i))
End If
Next
cell.Offset(0, 4).Value = dSum * -1
Next
End Sub



"Tom Ogilvy" wrote:

Select the cells in the column that have this construct and run the macro
or
change selection to something like Range("B2:B30")

Sub aaa()
Dim cell As Range, v As Variant
Dim dSum As Double, i As Long
For Each cell In Selection
dSum = 0
v = Split(Replace(cell, "+", ","), ",")
For i = LBound(v) To UBound(v)
If IsNumeric(v(i)) Then
dSum = dSum + CDbl(v(i))
End If
Next
cell.Offset(0, 5).Value = dSum
Next
End Sub

--
Regards,
Tom Ogilvy

"Brcobrem" wrote:

Hi,

I would greatly appreciate the assistance of you Excel mavens. Here's
what I
would like to accomplish:

1) My rows that have a field that looks like this (no quotes):
"TaskName, 12+75+42+5+36"
a. Note that the numbers above (ie. 12+75+42+5+36 in this
example)
vary from row to row. For example, the cell in the next row might look
like
this (no quotes):
"TaskSize, 10+22+84"

2) I would like to create a macro that would look at that cell and then
do
the addition on the numbers on the right (ie. 12+75+42+5+36 or
10+22+84),
while ignoring the text on the left (ie. TaskName, ) (ie. Taskname
comma
space)

3) I would then like the macro to put the result of the math in a filed
that
is 5 columns to the right.

This may be pretty easy, but I am not quite certain how to accomplish
the
parsing of the variable amount of numbers and plus signs within the
field. My
guess is that somehow Excel can read the cell form right to left, and
then
know to stop when it sees the ", " (ie. comma and a space).

I look forward to your thoughts on this one.

Regards,
Brcobrem