Looknsee Photography wrote:
Okay -- I'm lazy.  I've got a large MS Excel (2010) spreadsheet with a kinda database in it -- it has about a dozen columns and several hundred rows.  The spreadsheet is sorted alphabetically on the info in the first column.

I want a macro that works like this:
...  I put a partial text string in a spare cell.
...  I fire up a macro the reads that spare cell & scrolls the spreadsheet to that the closest match appears up at the top of the spreadsheet (right under the frozen panes that contain the labels for the columns).

Any hints about how to go about doing this?  I can get VLOOKUP to work to find the appropriate entry in the first column, but I don't know how to tell the spreadsheet to scroll down appropriately.

All help appreciated.

And here I thought you claimed to have a masters . . . . . . . My bad. Carry on!

Lohkee wrote:

And here I thought you claimed to have a masters . . . . . . . My bad. Carry on!

ouch

Lohkee wrote:

And here I thought you claimed to have a masters . . . . . . . My bad. Carry on!

Got anything relevant to contribute to the thread?

RW Steele wrote:
If you're able to find and put focus on the cell then try this:
Application. Goto ActiveCell.EntireRow,True

Thanks, but my question is how do I tell VB to put the focus on the row (stored in cell O2), column A?

Looknsee Photography wrote:
I challenge Lohkee to provide an answer written in Icelandic (or any other language he doesn't know).

[EDIT] Edited to convert my answer to Icelandic (which I don't know!) [/EDIT]

Auðvitað, VBA lausn í íslensku er algerlega eins.

Ég veit, ég veit, að þú vilt bara einhvern til að skrifa kóðann fyrir þig!

LightDreams wrote:
[EDIT] Edited to convert my answer to Icelandic (which I don't know!) [/EDIT]

Auðvitað, VBA lausn í íslensku er algerlega eins.

Ég veit, ég veit, að þú vilt bara einhvern til að skrifa kóðann fyrir þig!

Nei, ég þarf ekki einhvern til að skrifa kóðann fyrir mig. Ég þarf bara smá hjálp með því að nota gildi geymd í klefa sem ættingja eða alger heimilisfang.

I concede that Icelandic is not the same as Visual Basic.  I do have deep knowledge of computer science and have spent many years designing & maintaining all aspect of an operating system, including user interface (my favorite), spooler, memory manager, process manager, device drivers (wrote the first driver for a laser printer), and many more.  I'm conversant is a handful of programming languages, but Visual Basic isn't one of them.

One difference between Icelandic & Visual Basic -- with Icelandic, you can use Google Translate -- what's the Google Translate version the converts programming languages?

So sue me -- I have a specific question -- how to use a value stored in a cell to highlight a cell & move it to the top of the unfrozen cells in an Excel spreadsheet.  If you have knowledge, please share.  If you want to throw around unconstructive jabs, I suggest you CAM.

Looknsee Photography wrote:
Okay -- I'm lazy.  I've got a large MS Excel (2010) spreadsheet with a kinda database in it -- it has about a dozen columns and several hundred rows.  The spreadsheet is sorted alphabetically on the info in the first column.

I want a macro that works like this:
...  I put a partial text string in a spare cell.
...  I fire up a macro the reads that spare cell & scrolls the spreadsheet to that the closest match appears up at the top of the spreadsheet (right under the frozen panes that contain the labels for the columns).

Any hints about how to go about doing this?  I can get VLOOKUP to work to find the appropriate entry in the first column, but I don't know how to tell the spreadsheet to scroll down appropriately.

All help appreciated.

do while lenIcell(row,ligne)) > 0
   i =   InStr( [start], string, substring, [compare] )
  if i > 0
    activecell =
end if
loop

or something

Looknsee Photography wrote:
...  Cell O2:  That's where I type in the search characters.
...  Cell N2:  Has the formula "=VLOOKUP(O2,A4:M671,1)" which returns the contents of the best
     match to the search criteria.
...  Cell N3:  Has the formula "=MATCH(O2,A4:A671)+3" which returns the row that best matches the
     search characters in Cell O2.  The "+3" is needed to skip past the top 3 frozen rows.

I wrote a simple macro using the information stored in Cell N3.  It is as follows:

     Sub Find_Row()
         Dim Counter As Integer
         Counter = Range("N3").Value
         Rows(Counter).Select
    End Sub

It works well, but there are a couple of things that I need to tweak:

1)  I've hard-coded the size (number of rows) in the "database", in this case "671".  But I'm often adding rows to the "database", so eventually this won't work.  I've found that by changing the "671" to (say) "2000" has no adverse effect on the functionality of the macro.  The macro doesn't need to change at all -- if necessary, I'd have to update the MATCH and VLOOKUP formulas.  But at 2000, that'll be a long time away.

2)  The "Rows(Counter).Select" line selects the whole row, which is what I wanted.  But sometimes, I scroll horizontally, the look at the columns that fall off to the right, sometimes outside of the Excel window.  When the "Rows" line executes, the window is scrolled all the way to the left (kinda resetting the horizontal scroll, if any).  Anyone with any ideas how to preserve the horizontal scrolling with a select the entire row?


I worried about using a search criteria off the end of the database:  for example, if the last entry was for "Zoo", what would happen if I searched for "ZZZ"?  No worries, as written, it'll just highlight the last row, the one for "Zoo".  As a programmer, I was always making what I call "Off By One" errors like that, so I developed the habit of testing all the boundaries.

Looknsee Photography wrote:
Anyone with any ideas how to preserve the horizontal scrolling with a select the entire row?

Found on Google so I can't personally vouch for them, but there appears to be at least 2 possible approaches that might be useful:

- Possibly defining the worksheet scroll area. (<worksheetname>.ScrollArea).   Try a Google search for "excel macro freeze scroll".

- The other theoretical possibility is to programically restore your previous scrolling position after Excel changes it.  See:
http://www.automateexcel.com/2004/08/25 … _scroll_h/

For what it's worth!

LightDreams wrote:

Found on Google so I can't personally vouch for them, but there appears to be at least 2 possible approaches that might be useful:

- Possibly defining the worksheet scroll area. (<worksheetname>.ScrollArea).   Try a Google search for "excel macro freeze scroll".

- The other theoretical possibility is to programically restore your previous scrolling position after Excel changes it.  See:
http://www.automateexcel.com/2004/08/25 … _scroll_h/

For what it's worth!

Those are tasty hints, but I think I have to ruminate a bit more.

The problem is that when I execute the "find row" macro, I don't know how far I've scrolled horizontally.  Your hints tell me how to scroll to a specific row and/or column, but I don't (yet) know how to record the currently scrolled position before I select the appropriate row.  I'll keep looking -- it's an interesting problem, especially since I don't know VB.