Troubleshoot function

[mae0429]

Thread posted on VBA Express (thought I'd come here for a little extra help):
http://www.vbaexpress.com/forum/showthread.php?t=20372

Here's what the meat of my code is:

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 my 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. The function itself seemed to work for xld, but not on mine...

 

[schielrn]

Your missing a line from XLD's function and I think that is causing an error because myRange contains nothing when it is within the Union Function:

His function was:

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

You are missing what is in bold. Hope that helps.

 

[mae0429]

D'oh! Long day yesterday...must've been my eyes...

Thanks!

 

[Richard Schollar]

Hi

You are using an unqualified reference there - if you do not precede Range and Cells with the Sheet on which they reside then they refer to teh ActiveSheet which may not be what you want.

 Set myRange = Application.Union(myRange, Cells(iRow, ParameterNumber))

Cells above is unqualified - you need to specify the parent Sheet as well.

 

[Legacy 98055]

Set myRange = Application.Union(myRange, Cells(iRow, ParameterNumber))

myRange has no assignment prior to this line. Its value = Empty