Hi again Steve,

If the macro worked before then without going into what the entire macro
does, perhaps the following might help you.

What are you trying to return into iRowZ ? Is it the row number or a range?
The following line returns a row number and iRowZ should be dimensioned as
long (or could use integer if under about 32,000):-

iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row

However, to use the code with the dots in front of Cells and Rows.Count as
above, you need to have it nested in a With/End With statement like this:-

With Sheets("Sheet1")
iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row
End With

If not nested and you are allowing it to default to the active sheet then it
should look like this:-

iRowZ = Cells(Rows.Count, 1).End(xlUp).row

I am fresh out of ideas so perhaps you should start a new thread and post
your macro as you had it working previously plus the macro you have now with
a description of where it is failing and perhaps someone else will be able to
see what is wrong with it. (Include the entire macros with Dim statements).

--
Regards,

OssieMac


"SteveDB1" wrote:

Hi Ossie,
I suppose I need to make this statement.
The macro worked fine when I had constants for the iColMerge, iColFm,
iColTo, and iRowV variables.
I.e.,
Const iColMerge = n_1
Const iColTo = n_2
Const iColFm = n_3
Const iRowV = n_4 (where n_x are the constant numeric values I'd initially
chosen)

I now want to make these four items variables to have a userform to input my
choices.

I made a basic user form, and then connected the input text boxes.
Now I get a compile error-- a 424 error-- stating that the formula that I'd
initially given
iRowZ = .Cells(.Rows.Count, 1).End(xlUp).row
is no good.

My iniital assumption (I know, assumptions are not smart) was that my input
boxes have been incorrectly done.

And I only say that because the macro worked, and when I use constants,
still works.




"OssieMac" wrote:

Hi Steve,

Im sorry but without the data which is being manipulated, I am not able to
follow your code sufficiently to identify all the problems with it. However,
to answer some of your other questions.

The sample code that I gave you was only that. Sample code. It was only
meant to show you how to assign ranges and rows to variables because it
appeared to me that you were trying to assign a row number to a range
variable. I was trying to demonstrate that you cant assign a row number to a
variable dimensioned as a range and how you apply that information in your
code is up to you.

The reason for dimensioning numRow as long is because it must be numeric and
if dimensioned as an integer then it will only accommodate rows up to 32,767.
An interger is fine if you are certain that your code will not require a
larger number.

I have included some more code samples which might help you to decide how to
dimension a variable. You can leave the variable to default to a variant and
the sample code shows how to find out what type of variable VBA thinks it
should be.

With the Sheet names, it is up to you how you handle them in your
application. I like to be specific and use the Code names. However, I have
included some more sample code options on how you can address worksheets.

Code sample:-

Sub Demo_code_2()
'Finding what to dimension a variable as.
'Copy this code into module in a blank workbook.
'Initially let the variables default to Variant
Dim iRowZ
Dim iColMerge
Dim numbRow

'Note: The msgbox will show the following as long
'because it is greater than the integer range.
iColMerge = 33000
MsgBox "iColMerge is " & TypeName(iColMerge)

'Note: The msgbox will show the following as integer
'because it is less than the maximum integer range.
iColMerge = 32000
MsgBox "iColMerge is " & TypeName(iColMerge)


With Sheets("Sheet1")
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With
MsgBox "Irowz is " & TypeName(iRowZ)

End Sub



Sub Demo_code_3()
'Alternative methods of addressing worksheets.

Dim ws1 As Worksheet
Dim numbRow As Long
Dim iRowZ As Range

'Assign the active sheet to a variable
Set ws1 = ActiveSheet

'Use the variable in lieu of the worksheet name
With ws1
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

With ws1
'Note: Set is not used to return a row number
numbRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


'Using the code name of the worksheet. See the code name
'in the Project Explorer section of the VBA Editor.
'The code name is the one NOT in brackets. The name in
'brackets is the user defined name.
'The code name does not change when the user defined name
'is changed and is often a good reference to use in macros.

With Sheet1
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

'Using the worksheet index to reference a worksheet.
'Index is counted from the left as the tabs are displayed.
'If the sheets are re-ordered then the index is re-ordered.
With Sheets(1)
Set iRowZ = .Cells(.Rows.Count, 1).End(xlUp)
End With

End Sub


--
Regards,

OssieMac


"SteveDB1" wrote:

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: