Hi all.
I recently obtained help making a macro and it works great.
As I've further studied it, I realize now that in order for it to become
truly viable I need an input box.
Below is the copy of the macro (at the bottom of page).
My desire at this point is to have an input box ask for the starting row,
the merged column, and then the location of the column that I want to sum,
and where I want it placed.
I have in fact downloaded the VBA description of the inputBox Method-- from
the MS VBE help file.
So, my questions are as follows:
1- the function is written as:
expression.inputbox(prompt, Title, Default, left, top, ......)

What is the "expression" that I'd need to make this work? From what I can
gather, it seems that I'd need one of the statements from the macro itself.

One example it gives is MyNum = application.inputbox(prompt := "enter number")
So, to make the cross over to my macro, it seems that I'd want MyNum to
actually be "iColMerge" and I'd input the numeerica value of my choosing. Is
this correct?
Then for the second input I'd want "iColFm" and I'd input the numeric value
of my choosing.
And for the third input I'd want "iColTo" and I'd then input the numeric
value of my choosing.
For the fourth input I'd want "iRowV" and then input the numerica value of
my choosing.
How many inputs can I have in a single input box, to accomplish my goal of
all of my primary inputs? (I hope that's clear, it not, let me know.)
Then, if I can have multiple inputs witrhin a single input box, how would I
set that up?
Thanks again for your assistance.
Best.

-----------------------Main macro code----------------

<Sub Sub1()
' I only want it looking at the merged rows of column C.
Const iColMerge = 3
' values located in column D to be summed
Const iColFm = 4
' I want the sum valued results in column e
Const iColTo = 5
Dim zCell As Range, iRowV&, iRowZ&, iRowN&
' to find last used cell in column
iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
iRowV = 1
Do While iRowV <= iRowZ
Set zCell = Cells(iRowV, iColMerge)
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowN = iRowV + zCell.MergeArea.Rows.Count - 1
Cells(iRowV, iColTo).Formula = "=sum(" & _
Cells(iRowV, iColFm).Address & _
":" & _
Cells(iRowN, iColFm).Address & _
")"
iRowV = iRowN + 1 '
Else
iRowV = iRowV + 1
End If
Loop
End Sub

The expression you want is "Application". See your MyNum example.

There is no built in method for an input box to accept multiple inputs and
identify which is which. Of course multiple entries can be made into the
entry box, but it is then up to you to determine what was entered.

You can show the input box four times using a variant variable to accept the
return value and then assign it to each specific variable in turn.
Note that the input box returns "false" if the user clicks cancel.

Also, be aware that there are two different types of input boxes.
Omit the application prefix and the input box always returns a string.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"SteveDB1"
wrote in message
Hi all.
I recently obtained help making a macro and it works great.
As I've further studied it, I realize now that in order for it to become
truly viable I need an input box.
Below is the copy of the macro (at the bottom of page).
My desire at this point is to have an input box ask for the starting row,
the merged column, and then the location of the column that I want to sum,
and where I want it placed.
I have in fact downloaded the VBA description of the inputBox Method-- from
the MS VBE help file.
So, my questions are as follows:
1- the function is written as:
expression.inputbox(prompt, Title, Default, left, top, ......)

What is the "expression" that I'd need to make this work? From what I can
gather, it seems that I'd need one of the statements from the macro itself.

One example it gives is MyNum = application.inputbox(prompt := "enter number")
So, to make the cross over to my macro, it seems that I'd want MyNum to
actually be "iColMerge" and I'd input the numeerica value of my choosing. Is
this correct?
Then for the second input I'd want "iColFm" and I'd input the numeric value
of my choosing.
And for the third input I'd want "iColTo" and I'd then input the numeric
value of my choosing.
For the fourth input I'd want "iRowV" and then input the numerica value of
my choosing.
How many inputs can I have in a single input box, to accomplish my goal of
all of my primary inputs? (I hope that's clear, it not, let me know.)
Then, if I can have multiple inputs witrhin a single input box, how would I
set that up?
Thanks again for your assistance.
Best.

-----------------------Main macro code----------------

<Sub Sub1()
' I only want it looking at the merged rows of column C.
Const iColMerge = 3
' values located in column D to be summed
Const iColFm = 4
' I want the sum valued results in column e
Const iColTo = 5
Dim zCell As Range, iRowV&, iRowZ&, iRowN&
' to find last used cell in column
iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
iRowV = 1
Do While iRowV <= iRowZ
Set zCell = Cells(iRowV, iColMerge)
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowN = iRowV + zCell.MergeArea.Rows.Count - 1
Cells(iRowV, iColTo).Formula = "=sum(" & _
Cells(iRowV, iColFm).Address & _
":" & _
Cells(iRowN, iColFm).Address & _
")"
iRowV = iRowN + 1 '
Else
iRowV = iRowV + 1
End If
Loop
End Sub

Hi Jim,
Thank you for your response.
After I posted, I had remembered that we'd purchased the WROX big book for
VBA Programming in Excel, and read up further on this.
There was no specific discussion on my question about multiple choice
entries, and I stumbled acrossthe chapter on "User Forms."
I'm going to look farther into these as I think using a user form might be
closer to what I need.
If I need more answers I'll be back.
For now I'm leaving this topic open/unsolved.



"Jim Cone" wrote:


The expression you want is "Application". See your MyNum example.

There is no built in method for an input box to accept multiple inputs and
identify which is which. Of course multiple entries can be made into the
entry box, but it is then up to you to determine what was entered.

You can show the input box four times using a variant variable to accept the
return value and then assign it to each specific variable in turn.
Note that the input box returns "false" if the user clicks cancel.

Also, be aware that there are two different types of input boxes.
Omit the application prefix and the input box always returns a string.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"SteveDB1"
wrote in message
Hi all.
I recently obtained help making a macro and it works great.
As I've further studied it, I realize now that in order for it to become
truly viable I need an input box.
Below is the copy of the macro (at the bottom of page).
My desire at this point is to have an input box ask for the starting row,
the merged column, and then the location of the column that I want to sum,
and where I want it placed.
I have in fact downloaded the VBA description of the inputBox Method-- from
the MS VBE help file.
So, my questions are as follows:
1- the function is written as:
expression.inputbox(prompt, Title, Default, left, top, ......)

What is the "expression" that I'd need to make this work? From what I can
gather, it seems that I'd need one of the statements from the macro itself.

One example it gives is MyNum = application.inputbox(prompt := "enter number")
So, to make the cross over to my macro, it seems that I'd want MyNum to
actually be "iColMerge" and I'd input the numeerica value of my choosing. Is
this correct?
Then for the second input I'd want "iColFm" and I'd input the numeric value
of my choosing.
And for the third input I'd want "iColTo" and I'd then input the numeric
value of my choosing.
For the fourth input I'd want "iRowV" and then input the numerica value of
my choosing.
How many inputs can I have in a single input box, to accomplish my goal of
all of my primary inputs? (I hope that's clear, it not, let me know.)
Then, if I can have multiple inputs witrhin a single input box, how would I
set that up?
Thanks again for your assistance.
Best.

-----------------------Main macro code----------------

<Sub Sub1()
' I only want it looking at the merged rows of column C.
Const iColMerge = 3
' values located in column D to be summed
Const iColFm = 4
' I want the sum valued results in column e
Const iColTo = 5
Dim zCell As Range, iRowV&, iRowZ&, iRowN&
' to find last used cell in column
iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
iRowV = 1
Do While iRowV <= iRowZ
Set zCell = Cells(iRowV, iColMerge)
zCell.Select ' just to view
If zCell.MergeCells Then
' ck for merge type
If zCell.MergeArea.Columns.Count < 1 Then Stop ' ng
iRowN = iRowV + zCell.MergeArea.Rows.Count - 1
Cells(iRowV, iColTo).Formula = "=sum(" & _
Cells(iRowV, iColFm).Address & _
":" & _
Cells(iRowN, iColFm).Address & _
")"
iRowV = iRowN + 1 '
Else
iRowV = iRowV + 1
End If
Loop
End Sub