Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find all in vba
I run Excel 2003.
In columns("G:H"), I have: £8.00 £4.80 £8.00 -£0.90 £8.00 £2.70 I can identify that "-0.90" in VBA with Dim Overcharge As String Overcharge = Application.Min(Columns("G:H")) I want to identify all the cells which have that value. My example shows one, but there could be more. In Excel itself, I can select columns G and H and do Edit/Find... or Ctrl+F to get to the Find and Replace dialog, where I set Find What: to £-0.90 and click "Find All" to get a list of matching cells. When I record a macro doing this, it just records the selection. Is there a method which would return an array of matches? (I want to avoid the tedium of find and a findnext loop.) P.S. in VBA, I can refer equivalently to columns("G") and columns(7). I can also refer to columns("G:H"). Is there a numeric equivalent to columns("G:H")? columns(7,8) does not work. ;( I discount something like dim maxrow as long maxrow = application.max(cells(7,range(7,65536).end(xlUp).r ow, _ cells(8,range(8,65536).end(xlUp).row)) Range(cells(7,1), cells(8, maxrow)) I've just thrown that together and don't vouch for it doing as I intend. ;) -- Walter Briscoe |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming |