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 <dale....@nospam.com> 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
(E-Mail Removed)



"Dale Fye" <(E-Mail Removed)> wrote in message
news:B5143C07-184C-4089-BAA1-(E-Mail Removed)...
>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
> (E-Mail Removed)
>
>
>
> "Dale Fye" <(E-Mail Removed)> wrote in message
> news:B5143C07-184C-4089-BAA1-(E-Mail Removed)...
> >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
(E-Mail Removed)



"Dale Fye" <(E-Mail Removed)> wrote in message
news:95506793-4CF0-4196-8BB5-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Dale Fye" <(E-Mail Removed)> wrote in message
>> news:B5143C07-184C-4089-BAA1-(E-Mail Removed)...
>> >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.
>> >
>>