Solved: What's wrong with this function?

[mae0429]

I can't seem to get the union to work in the following (it fails with error "Method 'Range' of Object '_Global' failed" at the union line):

[vba]
Function SettingRange(ShockNumber As Long, ParameterNumber As Long, MaxRow As Variant) As Range
Dim iRow As Long
Dim myRange As Range
ParameterNumber = ParameterNumber + 9
For iRow = 2 + ShockNumber To MaxRow Step 7
Set myRange = Application.Union(myRange, Range(Cells(iRow, ParameterNumber)))
Next iRow
Set SettingRange = myRange
End Function
[/vba]

Any clues?
-Matt

[Bob Phillips]

[vba]

Function SettingRange(ShockNumber As Long, ParameterNumber As Long, MaxRow As Variant) As Range
Dim iRow As Long
Dim myRange As Range
ParameterNumber = ParameterNumber + 9
For iRow = 2 + ShockNumber To MaxRow Step 7
If myRange Is Nothing Then
Set myRange = Cells(iRow, ParameterNumber)
Else
Set myRange = Application.Union(myRange, Cells(iRow, ParameterNumber))
End If
Next iRow
Set SettingRange = myRange
End Function
[/vba]

[mae0429]

Trying that gives me "Invalid procedure call or argument" in the same line...

[Bob Phillips]

Works fine for me in my liited test.

[mae0429]

Here's what's going on in my code:

[vba]
Option Explicit
Sub CreateCharts()
Dim C, newRange As Range
Dim i, j, LastRow, numShts, numCols, m, n As Long
Dim activeShtName As String
Dim values As Variant
'Define number of sheets and number of paramters
numShts = 24
numCols = 12
'Create array of target values of parameters (blanks for text or date/time values)
values = Array(, , 0.33, 0.34, 0.31, , 0.85, 0.83, 0.22, 0.654, 0.438, 0.398)
'Locate last row on each sheet
For i = 1 To numShts
Worksheets("Sheet" & i).Activate
With ActiveSheet
LastRow = [G65536].End(xlUp).Row
End With
'If value is <10% or >10% of target value, make the value red and bold
For j = 1 To numCols
For Each C In Worksheets("Sheet" & i).Range(Cells(1, j), Cells(LastRow, j))
If IsNumeric(C) = True And IsEmpty(C) = False Then
If C.Value < values(j - 1) - 0.1 * values(j - 1) Or C.Value > values(j - 1) + 0.1 * values(j - 1) Then
C.Font.ColorIndex = 3
C.Font.Bold = True
End If
End If
Next C
Next j

'Set series to ranges
For m = 1 To 6
For n = 1 To 6
Set newRange = SettingRange(m, n, LastRow)
Next n
Next m
Next i
End Sub
[/vba]

With the function currently being:

[vba]
Function SettingRange(ShockNumber As Variant, ParameterNumber As Long, MaxRow As Variant) As Range
Dim iRow As Long
Dim myRange As Range
ParameterNumber = ParameterNumber + 9
For iRow = 2 + ShockNumber To MaxRow Step 7
Set myRange = Application.Union(myRange, Cells(iRow, ParameterNumber))
Next iRow
Set SettingRange = myRange
End Function
[/vba]

When I run it, I get "Runtime error '5', invalid procedure call or argument on the line where I set myRange. Any clues why it works for you and not me?

[Bob Phillips]

Asking us to work out what is going on, create test data to test it, and solve your problem is asking too much.

How about a workbook?

[mae0429]

Sorry xld, you nailed it the first time. Must've been my eyes after a long day yesterday: I didn't get all of your function. It works just fine now.

Thanks for always finding the answer! Marking as solved.