Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a macro to create excel line graph with coloured pointers anddata lables | Charts and Charting in Excel | |||
Using a macro to create a macro in another workbook | Excel Worksheet Functions | |||
could any one please tell me how can i create a macro that runs other macro | Excel Programming | |||
how to create a macro that runs other macro in the same workbook | Excel Programming | |||
Use existing macro to create another macro | Excel Programming |