Compare values of cells in a range

D

Dale Fye

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
 
P

paul.robinson

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
 
N

Nigel

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
 
M

merjet

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
 
D

Dale Fye

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
 
N

Nigel

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 address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top