PDA

View Full Version : Solved: Range(????).Find on Hidden Columns



CBrine
11-27-2006, 12:24 PM
I don't use the find very often programmatically within VBA, but decided to do this with a project I'm working on, since I know it's faster then cycling through cells.
I have a summary with unique values based on 2 columns, which I create in Column A by linking the two other columns together.

A......B......C
12.....1......2
13.....1......3
21.....2......1

etc....
I then hide column A so that it doesn't show up in the column, since I don't want the user to see the unique index I'm using.

The problem occurs when I try and execute a find on the hidden column. It doesn't seem to work?
Has anyone found a way around this, other then unhiding, then re-hiding the column? or am I stuck with it?

Thanks
Cal

mdmackillop
11-27-2006, 12:55 PM
Hi Cal
You could try Match, which will return the index of the matched cell.

Option Explicit
Sub Macro1()
Dim Rng As Range
Dim MyStr As String
Dim Test As Long
MyStr = InputBox("Text to find")
Set Rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Test = Application.WorksheetFunction.Match(MyStr, Rng.Value)
MsgBox Test
End Sub

CBrine
11-27-2006, 01:20 PM
md,
Did some quick testing of the processing times using
Match
Find
Cell Cycling
It looks like the vba match wins hands down at about 1/40th the time it takes for the other two methods. It's quick. Think I will go with it.

Thanks md.

mdmackillop
11-27-2006, 01:25 PM
HTH,
But also check Match in Help for the MatchType to make sure you get the answer your looking for.

CBrine
11-27-2006, 01:47 PM
md,
Thanks for the head's up. I was looking for an exact match(0). Surprised it doesn't default to that.

Cal