Quick Question ABout Setting a Range

[Saladsamurai]

Okay then :hi:

I am trying to modify this:
Set ErrorRange = Range(Cells(1, 1), Cells(nRow, nCol))

So that I can use it from another worksheet. I tried this:

Set ErrorRange = Range(WorkSheets(5).Cells(1, 1),WorkSheets(5). Cells(nRow, nCol))

But as you probably know, that did not work.

Any ideas?
Thanks!!

[Saladsamurai]

I tried it this way too, but no dice :

http://i12.photobucket.com/albums/a220/saladsamurai/errrror.jpg

I know that the error is caused by this line. WHen I comment out the line, the rest of the program executes.


Here is the whole code if needed:

Option Explicit
Private Sub CommandButton1_Click()

Dim i, j, nRow, nCol, G, Y, R, TotalRacks As Integer
Dim xgg, xgy, xgr, xyg, xyy, xyr, xrg, xry, xrr As Integer
Dim OverPredicted, MyError, Percent, WithInTenPercent As Double
Dim MyMax, ErrorSquare As Double
Dim ErrorRange As Range

' *********************************************************************
' *********************************************************************
' Count # of times ISX is conservative or not


nRow = 25
nCol = 32

xgg = 0
xgy = 0
xgr = 0
xyg = 0
xyy = 0
xyr = 0
xrg = 0
xry = 0
xrr = 0
G = 0
Y = 0
R = 0

MyMax = 0
Percent = 0
OverPredicted = 0
WithInTenPercent = 0
ErrorSquare = 0
'' Check # times CFD predicts Green and ISX predicts Green, Yellow, or Red
' For i = 1 To nRow
' For j = 1 To nCol
'
'
' If Worksheets("Best CI CFD").Cells(i, j) >= 0.9 Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xgg = xgg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xgy = xgy + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xgr = xgr + 1
'
' G = G + 1
'
' End If
'
' ElseIf Worksheets("Best CI CFD").Cells(i, j) < 9 And Worksheets("Best CI CFD").Cells(i, j) > 0.8 Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xyg = xyg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xyy = xyy + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xyr = xyr + 1
'
' Y = Y + 1
'
' End If
'
' ElseIf Worksheets("Best CI CFD").Cells(i, j) <= 0.8 And Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' If Worksheets("Best CI ISX").Cells(i, j) >= 0.9 Then
' xrg = xrg + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) < 0.9 And Worksheets("Best CI ISX").Cells(i, j) > 0.8 Then
' xry = xry + 1
' ElseIf Worksheets("Best CI ISX").Cells(i, j) <= 0.8 And Worksheets("Best CI ISX").Cells(i, j) <> "" Then
' xrr = xrr + 1
'
' End If
'
' R = R + 1
'
' End If
'
' Next j
'
' Next i
' *********************************************************************
' *********************************************************************
' Count # times ISX Overpredicted; Is w/in 10 %; max error; etc

For i = 1 To nRow
For j = 1 To nCol
If Worksheets("Best CI CFD").Cells(i, j) <> "" Then
' Compute Difference: ISX_CI - CFD_CI
MyError = Worksheets("Best CI ISX").Cells(i, j) - Worksheets("Best CI CFD").Cells(i, j)


If MyError > 0 Then
OverPredicted = OverPredicted + 1 'Overpredicted means ISX predicted better cooling
End If 'performance than actual (i.e. CFD)

' Compute % Error by dividing by actual (i.e. CFD)
Percent = Percent + Abs(MyError) / (Worksheets("Best CI CFD").Cells(i, j) + 0.0000000001) 'Prevents a div by 0 error

If Abs(MyError) > MyMax Then
MyMax = Abs(MyError)
End If
' Count #times ISX is w/in 10 % of CFD
If Abs(MyError) <= 0.1 Then
WithInTenPercent = WithInTenPercent + 1
End If

' Calculate error-squared-sum for RMS calculation
ErrorSquare = ErrorSquare + MyError ^ 2
End If
Next j
Next i

Set ErrorRange = Worksheets("Best CI CFD").Range(Cells(1, 1), Cells(nRow, nCol))
TotalRacks = Application.WorksheetFunction.Count(ErrorRange)
MsgBox TotalRacks
End Sub

[tpoynton]

try:
with thisworkbook.sheets(5)
Set ErrorRange = .Range(.Cells(1, 1), .Cells(nRow, nCol))
end with

[D_Rennie]

should nrow and nclo be long not integer?

just done a little digging at it seams that excel will convert integer to long automatically even though this conversion is done if the limitation if integer is reached it will still throws a overfolw error.

[Saladsamurai]

should nrow and nclo be long not integer?

Why? They are both <100

[D_Rennie]

Didnt realy look at the code.

[Saladsamurai]

try:
with thisworkbook.sheets(5)
Set ErrorRange = .Range(.Cells(1, 1), .Cells(nRow, nCol))
end with

What are we defining "thiswoorkbook" to be?

[tpoynton]

see http://msdn.microsoft.com/en-us/library/aa164754(office.10).aspx . Long is converted to integer, so there is a slight performance advantage...

RE: defining thisworkbook...did you try it? just my habit...using 'with worksheets(5)' would probably work.

[Saladsamurai]

see http://msdn.microsoft.com/en-us/library/aa164754(office.10).aspx . Long is converted to integer, so there is a slight performance advantage...

RE: defining thisworkbook...did you try it? just my habit...using 'with worksheets(5)' would probably work.

I tried
With WorkBooks("NAME").WorkSheets("NAME")
Set.....
End With

and got error again

[tpoynton]

did you try the code as I had posted it?

[Saladsamurai]

did you try the code as I had posted it?

I did and still got the/an error. I forget how I got around it.

What part of MA you in?