PDA

View Full Version : Solved: What's wrong with this function?



mae0429
06-23-2008, 02:19 PM
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):


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


Any clues?
-Matt

Bob Phillips
06-23-2008, 02:22 PM
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

mae0429
06-23-2008, 02:25 PM
Trying that gives me "Invalid procedure call or argument" in the same line...

Bob Phillips
06-23-2008, 02:53 PM
Works fine for me in my liited test.

mae0429
06-24-2008, 06:42 AM
Here's what's going on in my code:


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


With the function currently being:


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


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
06-24-2008, 07:30 AM
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
06-24-2008, 07:34 AM
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.