Solved: Best way to select these ranges w/ macro?

[mae0429]

I posted this on Mr. Excel, but it hasn't really gotten any attention (that place is rather busy...)

http://www.mrexcel.com/forum/showthread.php?t=325999

Basically, I'm trying to write a macro that will create a set of ranges (ideally named because I need to refer to them to create a graph). The hitch is that the data is part of a series of 6, and there are 6 columns of such data. This is a sample of the data (same set is duplicated for simplicity):

(Took out incomprehensible data...see attachment in my next post)

So what I would like to do, is take all of the "A"s from column 1 and put them in a range, then all the "B"s from column 1 and put them in another range...etc. Then, move to column 2 and repeat. The number of rows is going to be variable, so I've used a line or two to find the last row and it's stored as a variable.


Thanks, and good luck!

-Matt

[Bob Phillips]

Do you want to have another go at laying that data out, it is incomprehensible.

[mae0429]

Sorry, it formatted correctly on the other site...let's try this again...

[mae0429]

Okay, I've worked on some code to try to get what I want, but I'm having an issue calling my function...


Sub CreateCharts()
Dim C, rrange As Range
Dim i, j, LastRow, numShts, numCols As Long
Dim activeShtName As String
Dim values As Variant
'Define number of devices 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 = [C65536].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

'Define ranges to span parameter values
Set rrange = SettingRange(3, 2, LastRow)
Next i

End Sub


Now, the SettingRange function is:

Function SettingRange(ParameterNumber As Long, ShockNumber As Long, MaxRow As Long) As Range
Dim myVar(1 To 29, 1 To 6) As Double
Dim myRange As Range
Dim iRow, iCol As Long
Dim cellString As String
count = ShockNumber
' While count <= MaxRow
For iRow = LBound(myVar, 1) To UBound(myVar, 1)
For iCol = LBound(myVar, 2) To UBound(myVar, 2)
myVar(iRow, iCol) = Cells(iRow, iCol)
MsgBox myVar(iRow, iCol)
Next iCol
Next iRow
myRange = Cells(count, ParameterNumber)
count = count + 7
' Wend
Set SettingRange = myRange
End Function



I took out the while loop for now, I'm still up in the air on that one.

When I try to run this, I get a compile error with the whole ByRef argument type mismatch on passing LastRow. I thought that making it Long and then setting up the function to receive a Long type variable, it wouldn't give me this error...guess I thought wrong.

[Bob Phillips]

That is because you default the LastRow in the calling module to variant, the argument type in the called module must also be a variant, not long.

[mae0429]

Okay, I'm going an entirely different way, so I'm marking this as solved.