I want to compare the values of cells in a range, without actually selecting
the range. If so, what is the proper syntax? BTW, the sheet that the range
is on is hidden.

Private Function fnCompCells(rng as Range) as String

For each cell in rng
Dim myValue as string
If cell.value .....
'insert some comparison code here
endif
Next
fnCompCells = myValue
End
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

On Dec 14, 2:18 pm, Dale Fye wrote:
I want to compare the values of cells in a range, without actually selecting
the range. If so, what is the proper syntax? BTW, the sheet that the range
is on is hidden.

Private Function fnCompCells(rng as Range) as String

For each cell in rng
Dim myValue as string
If cell.value .....
'insert some comparison code here
endif
Next
fnCompCells = myValue
End
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hi
Not quite sure what you want, but you would use this function like:

Set myRange = Worksheets("MyHiddenSheet").Range("A2:C10")
myString = fnCompCells(myRange)

regards
Paul

Not sure this is what you are asking but the following compares and does not
select first and works for hidden or very hidden sheets. If you are
comparing strings you might like to Trim the values to remove leading and
trailing spaces.


If cell.value = anothercell.value then
' do something
else
' do something
end if

--

Regards,
Nigel




"Dale Fye" wrote in message
...
I want to compare the values of cells in a range, without actually
selecting
the range. If so, what is the proper syntax? BTW, the sheet that the
range
is on is hidden.

Private Function fnCompCells(rng as Range) as String

For each cell in rng
Dim myValue as string
If cell.value .....
'insert some comparison code here
endif
Next
fnCompCells = myValue
End
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

The following Sub calls your Function with the rng set,
but not "selected", by an InputBox. It could be set in
other ways as well.

Public Sub FindMax()
Dim str1 As String
Dim rng As Range
str1 = InputBox("Enter range of cells, e.g. A1:A10.")
Set rng = Range(str1)
Debug.Print fnCompCells(rng)
End Sub

Hth,
Merjet

Guys,

I guess I wasn't clear enough. The code I posted was not working. Excel
was not recognizing Cell as a valid object, and was giving me range errors as
well. I ended up using:

Public Function fnMaxClass(rng As Range)

Dim obj As Object
Dim myMax As String
Dim bIsVisible As Integer, bScreenUpdates As Boolean

bIsVisible = ActiveWorkbook.Sheets("TaskStandards").Visible
bScreenUpdates = Application.ScreenUpdating

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("TaskStandards").Visible = True
ActiveWorkbook.Sheets("TaskStandards").Activate

rng.Select
For Each obj In Selection
'insert comparison code here
Next

ActiveWorkbook.Sheets("TaskStandards").Visible = bIsVisible
Application.ScreenUpdating = bScreenUpdates

fnMaxClass = myMax

End Function

I was hoping to avoid all of those additional steps

Dale
--
email address is invalid
Please reply to newsgroup only.



"Nigel" wrote:

Not sure this is what you are asking but the following compares and does not
select first and works for hidden or very hidden sheets. If you are
comparing strings you might like to Trim the values to remove leading and
trailing spaces.


If cell.value = anothercell.value then
' do something
else
' do something
end if

--

Regards,
Nigel




"Dale Fye" wrote in message
...
I want to compare the values of cells in a range, without actually
selecting
the range. If so, what is the proper syntax? BTW, the sheet that the
range
is on is hidden.

Private Function fnCompCells(rng as Range) as String

For each cell in rng
Dim myValue as string
If cell.value .....
'insert some comparison code here
endif
Next
fnCompCells = myValue
End
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Try it this way......

Private Function fnCompCells(rng As Range) As String
Dim myValue As String, Cell As Range
For Each Cell In rng
If Cell.Value = ........ Then
'insert some comparison code here
End If
Next
fnCompCells = myValue
End Function



--

Regards,
Nigel




"Dale Fye" wrote in message
...
Guys,

I guess I wasn't clear enough. The code I posted was not working. Excel
was not recognizing Cell as a valid object, and was giving me range errors
as
well. I ended up using:

Public Function fnMaxClass(rng As Range)

Dim obj As Object
Dim myMax As String
Dim bIsVisible As Integer, bScreenUpdates As Boolean

bIsVisible = ActiveWorkbook.Sheets("TaskStandards").Visible
bScreenUpdates = Application.ScreenUpdating

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("TaskStandards").Visible = True
ActiveWorkbook.Sheets("TaskStandards").Activate

rng.Select
For Each obj In Selection
'insert comparison code here
Next

ActiveWorkbook.Sheets("TaskStandards").Visible = bIsVisible
Application.ScreenUpdating = bScreenUpdates

fnMaxClass = myMax

End Function

I was hoping to avoid all of those additional steps

Dale
--
email address is invalid
Please reply to newsgroup only.



"Nigel" wrote:

Not sure this is what you are asking but the following compares and does
not
select first and works for hidden or very hidden sheets. If you are
comparing strings you might like to Trim the values to remove leading and
trailing spaces.


If cell.value = anothercell.value then
' do something
else
' do something
end if

--

Regards,
Nigel




"Dale Fye" wrote in message
...
I want to compare the values of cells in a range, without actually
selecting
the range. If so, what is the proper syntax? BTW, the sheet that the
range
is on is hidden.

Private Function fnCompCells(rng as Range) as String

For each cell in rng
Dim myValue as string
If cell.value .....
'insert some comparison code here
endif
Next
fnCompCells = myValue
End
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.