Ok, the complete macro, including user form portion.
Thank you.
-----------------------------------------------
'This function will sum values of merged cells, without having to go
'through each line of data for summing manually.
'And yes, it works now, as written.
Sub IF_Merged_Sum(Optional control As IRibbonControl)
' the (Optional control as iRibbonControl) will ultimately allow it to be
accessed through a ribbon menu I've made.
' I only want it looking at the merged rows of column C (owners'
'names). Can be changed from column C to more distant columns by changing
'numeric value.
Dim iColMerge As Variant
TxtCol1 = iColMerge
'TxtCol1 is user input box and supposed to assign value input by user to
iColMerge.
' values located in column D to be summed. Can also be changed to
'column E, or F by changing from 4 to 5, or 6.
Dim iColFm As Variant
TxtCol2 = iColFm
'TxtCol2 is user input box and is supposed to assign value input by user to
iColFm
' I want the sum valued results in column G. Can be changed from G to
'H, I, etc... by changing numeric value.
Dim iColTo As Variant
TxtCol3 = iColTo
'TxtCol3 is user input box and is supposed to assign value input by user to
iColTo.
Dim zCell As Range, iRowZ&, iRowN&, iRowV&,
' to find last used cell in column. Will stop at last used row.
'With Sheets("Sum")
'Set
'iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row
Dim NumRow As Long
NumRow = Cells(Rows.Count, iColMerge).End(xlUp).Row
'End With

' Dim iRowV As Variant- already declared as Range.
TxtRow1 = iRowV
'TxtRow1 is suppose to assign user input value to iRowV.
'set this number at starting row of ownership values. If
'further down than 4th row, set to that value, i.e., 9th, 10th, etc....
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 ' Found error, and
'stops.
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


Private Sub TxtCol1_Change()

End Sub

Private Sub TxtCol2_Change()

End Sub

Private Sub TxtCol3_Change()

End Sub

Private Sub TxtRow1_Change()

End Sub

Private Sub UserForm_Click()

End Sub
Option Explicit
Public Cancelled As Boolean

Private Sub CmdCancel_Click()
Cancelled = True
Me.Hide
End Sub

Private Sub CmdOK_Click()
Call IF_Merged_Sum
Cancelled = False
Me.Hide
End Sub

-------------------------------------------------

"OssieMac" wrote:

Hi Steve,

Your code was trying to assign a row number to a range variable and it was
missing 'Set' which is required with objects assigned to variables. Study the
following and see what you can make of it.

Feel free to get back to me if you still have any questions.


Dim Irowz As Range
Dim iColMerge As Long
Dim numbRow As Long

iColMerge = 1 'Set to column A for testing

'Following assigns (Sets) range to a variable
'Note 'Set' is required at start of line.
'It will be the last non blank cell in column A
Set Irowz = Cells(Rows.Count, iColMerge).End(xlUp)

'Better (preferred) method. Note dot in front of cells and rows.count
With Sheets("Sheet1")
Set Irowz = .Cells(.Rows.Count, iColMerge).End(xlUp)
End With


'Following assigns entire row to a variable where
'it finds the last non blank cell in column A
Set Irowz = Cells(Rows.Count, iColMerge).End(xlUp).EntireRow

'Following assigns entire range to variable from A1
'to end of data in column A
'Note: Space and underscore is a line break in what
'is otherwise a single line of code
With Sheets("Sheet1")
Set Irowz = .Range(.Cells(1, iColMerge), _
.Cells(.Rows.Count, iColMerge).End(xlUp))
End With


'Following assigns row number to a variable
'It will be the row number of last non blank cell in column A
'Note: 'Set' not used here because it is not an object;
'simply a number
numbRow = Cells(Rows.Count, iColMerge).End(xlUp).Row



--
Regards,

OssieMac


"SteveDB1" wrote:

Ossie,
Thank you for your help.
While I believe that I got the text boxes linked to my macro, I have another
item that's not working now.
The code for this component (and my understanding of it's task) is below:

iRowZ = Cells(Rows.Count, iColMerge).End(xlUp).Row

iRowZ is declared as a range.
iColMerge is a variant.
my understanding-- and please tell me if I'm wrong, and correct me-- of the
main component here is
iRowZ = Cells(Rows.count, iColMerge).End(xlUp).Row
It counts the rows in the column of choice through to the end-- row by row.
The error that I'm getting is the 1004 error.
Last night when I first found it it said it was a global failure.
Now it's calling it a "Application-defined or object-defined error"
When the line of code is highlighted yellow,
iRowZ = 0, Rows.count = 1048576, iColMerge = 3, and .End(xlUp) = -4162
First, I would've thought that iRowZ would've started below the row that I
chose for iRowV.
Next, my goal of the macro was to have it only run to where the filled rows
stopped.
Yet Rows.count goes all of the way down to the very end of the worksheet.
Then, if I understand .End(xlUp) correctly, it backs up from the end of
Rows.count the amount specified.
Again, thank you.


"OssieMac" wrote:

Hi Steve,

The book means to use a name that means something to you like GivenName,
SurName, Address etc. in lieu of TextBox1, TextBox2 etc.

When you right click on the text box in the design mode it will take you
into the VBA code area for them and automatically name a sub that will become
the event when you change the value or text in the text box.

Note that you have drop down boxes at the top of the VBA editor where you
can select the text box in the left drop down and in the right drop down you
select what type of action for which you want an event to take place.
Selecting these will automatically create the sub name and end sub between
which you enter the required code. Example: Select the text box by name in
the left drop down and then Exit in the right dropdown. The code for this
takes place when you have finished in the text box and exit out of it. In the
change event, the code takes place as you change it.

The following are examples of code to copy the data from the text boxes to
cells on the worksheet. Note that both .Text and .Value appear to work the
same. Someone might be able to provide more information on whether there are
any subtle differences but I have never found any.

Private Sub TextBox1_Change()
Sheets("Sheet1").Range("A10") = UserForm1.TextBox1.Text

'Alternative code using Value in liew of Text
'Sheets("Sheet1").Range("A10") = UserForm1.TextBox1.Value
End Sub


If you rename the text box from TextBox1 to MyTextBox, it would look like
this:-

Private Sub MyTextBox_Change()
Sheets("Sheet1").Range("A10") = UserForm1.MyTextBox.Text

'Alternative code using Value in liew of Text
'Sheets("Sheet1").Range("A10") = UserForm1.MyTextBox.Value
End Sub


Hope it helps and feel free to get back to me if you have any further
questions.

--
Regards,

OssieMac


"SteveDB1" wrote:

Hi all.
I have a macro that I want to use a user form with.
I've used the VBA editor to-- drag/drop-- build a form, and I need to link
the input text boxes to the functions of the macro so that it will make the
macro work the way that I want.
My questions a
How do I need to do that?
According to the WROX VBA Excel 2007 programmers' reference book- chapter
13, I was to rename the text boxes to something that would make them
recognizable.
So, does that mean that my input boxes need to be named identical to the
macro's variable names?
I.e., if my macro variables are named: iRowV, iColFm, iColTo, iColMerge, am
I to name the text boxes those names?
Thanks for your help.