sendkeys - move cursor down on filtered row on sheet 1

[littlewoman]

Using VBA - Excel 2003

in short:
1 worksheet, lots of records on rows.
trying to get 1 or more records

first I used:
while activecell.value <> searchvalue then
activecell.offset(1,0).select
wend

works perfectly but with a large database it becomes slow on the less powerfull machines

now I thought using the autofilter.
but activecell.offset(1,0) doesnt recognize the filtered out rows and so the autofilter wend useless in this.

yet when you push on de downarrow-key by hand it jumps to the first filtered row
searching which code might do this trick, I found sendkeys
but it doesn't work,

Sheets(2).Select
Range("a1").Select
Selection.AutoFilter field:=1, Criteria1:=searchvalue
Application.SendKeys ("{DOWN}")

the space which occurs seems to indicate that there need to be more code but I don't know what.

not sure of the syntax involved I have tried also
SendKeys("{DOWN}") - no result
SendKeys("DOWN") - printed DOWN on codeline
Application.SendKeys(DOWN) - no result
Application.SendKeys {DOWN}- warning on usage "{"

so now I'm lost.
Am I making a syntax error or is sendkeys not to be used for this or what?

 

[Zathras]

You are right. this is not what sendkeys is used for.

It would help if you described the problem instead of a supposed solution.

Most likely what you should use is the .Find method of whatever range you are searching.

(check the help file for the "Find Method")

 

[littlewoman]

Thank you for your solution of .find. It's faster than searching row by row indeed.

And thank you for clearing up the sendkeys usage.

in answer to your remark about problemdescription:
quote:
it becomes slow on the less powerfull machines

that is a problem isn't it?

If I had leave it with that, as I have done before, the first thing I would have gotten back is the valid question: what have you tried and what code did you use. Well to save everybody time and efforts, it is all there now:
What I use
What I have
What I want
What the problem is
What I've tried
and what happened trying
and I've tried to keep it clear.

Maybe I have failed in the last instance but instead of you reading properly, you come back condensending and that irritated me.

Maybe I'm a child in vba and you are the adult
but you have forgotten what it is like to be a child when all is overwelming and the obvious is not clear in an instance. And to put down all you've tried and come up with is not such an easy task at it seems.

Nobody has ever died from a little kindness as most of your collegue-experts here seem to know.

so thank you for your help and pointing my nose in the wright direction, I'll give you a star for it, 'cause in the last week of searching internet for a solution, I have seen many questions like this but no answer that worked and this one does.

goodday

 

[Zathras]

Please accept my apologies.

What I meant was, the closest thing to a problem statement you gave (at least in my view) was

1 worksheet, lots of records on rows.
trying to get 1 or more records

Everything else in the post was related to one or two possible solutions, namely stepping thru a column selecting one cell at a time. It is very difficult to recommend a solution without knowing why you need to "get 1 or more records"
for counting?
for printing?
for copying to another sheet?
for accumulating one or more values in the row?
for highlighting to display to the user?

Lacking that knowledge, all I could do was address your first solution and provide a little help on how to speed up that process. Whether that actually provides the best solution to the original problem, I have no way of knowing. Perhaps another technique would be better for whatever it is that you are needing to do: Advanced Data Filter, Pivot Table, Database Techniques, accessing the sheet like a table using ODBC, sorting, or something completely different than Excel, to name a few.

I'm truly sorry that I came across as condescending. That is never my intention in any post. (Sarcastic sometimes, but never condescending.)

It appears that our differences are a result of using the word "problem" in two different ways. To you, the problem was "it becomes slow." To me the problem is "the task that the user has presented to you." I can see how it would be irritating to be asked for a problem statement when it is thought that one has already been given.

I have been burned in the past by a programmer asking for assistance with a particular technique without specifying what the total job was. When it turned out to be the wrong technique to use, the programmer said to our boss, "He told me to do it that way." Now, that is truly irritating.

 

[littlewoman]

well, then plse accept my appologies also.
this seems to be a miscommunication going sour.

hm,
it seems I'm more outdated then I first figured. lol
the jump from q-basic to visual basic is larger then I assumed at first glance in that way that not only the commands, I dó know, and their syntaxes changed but also the way to approach things.

I will keep that in mind the next time I post. thnx for clearing this up.

In answer:
To burn people for the only reason that the solution they gave didn't work for you is sickening. It lacks a sence of responsibility for your own doing. People should bare in mind that A solution given need not be THE solution needed and in the end YOU implement the code not the helper.
I'm sorry that you helped someone who wasn't worth helping.

Now, keeping the advice in mind, for anyone who read this and has the time to read all of this:
.find worked 'cause I was trying to do the following basics:
sheet 1 contains column A: IDno, column B: Names etc.
sheet 2 contains column A: RecordNo. column B: IDno etc.

(in short a database like access where the tables are sheets, each record unique by recno and linked by IDno)

an userform to add stuff to sheet 2, 3 etc. contains combobox cbxIDno, filled with column A of sheet 1, combobox cbxName, filled with comlumn B of sheet 1, and more controls for other stuff

now if someone chooses a idno, the code starts searching for the name, and produces this as a checkup.
that's the easy part since the idno is unique on sheet 1, so: Range("a1").select: cbxNaam.value = activecell.offset(val(cbxIDno),1).value
works quick and effective

the problem started when somebody chose a name, I need to get the IDno for linking other data to that person so I need to 'pull' the record with the name and 'read' the idno but in this case the rowno. on which the record is stored is unknown
so I tried:
Range("b1").select
While activecell.value <> val(cbxName)
activecell.offset(1,0).select
Wend
cbxName = activecell.value

that worked but when the number of rows filled grew this way of doing slowed it down upto several minutes.

the .find method as given by Zathras is perfect for this kind of thing. It works as fast as manually using the dropdown of the autofilter on the sheet.

Now for the ones who want to use .find also, this is what I found out already:

you can use the example as given in the helpfiles of excel
and to target a cell other than the one you find you use:
c.offset([no. of rows], [no. of columns]).value instead of c.value
so in my example it was cbxIDno.value = c.offset(0,-1).value

furthermore I found it very interesting that if I used a counter between 'do' and 'loop ...': cnt=cnt+1, I could use temp variables in array
ie recno(cnt) = c.offset(0,-1).value
so the multiple unique recordno's linked to the person with his/her IDno. on the other sheets could be 'pulled' for later use.

the only thing that differs from the activecell.offset is that with cell you select a new cell so the next time you need to 'offset' you go from that new cell, with .find the offset is always form the cell you have found. so if you searched column a, and you need next column B offset = (0,1) but next you need column c then offset = (0,2)

with other words this is like the INDEX / MATCH formula which you can use on a sheet to a sheet but then in vba-code to vba-code

simple, fast and effective.

maybe there are even more sophisticated solutions available but with my limited knowledge (yet, ghegheghe) it's sufficiant to my needs as they are now.

And Zathras thnx for resolving this miscommunication, I'm feeling silly now (hihihi, oh well) but I gained a lot of respect for you.

Hope to see you again the next time I 'will' get stuck. lol

have fun

 

[Zathras]

Thanks for the response. I'm glad we can see eye to eye. The "burn" was a long time ago (not on Tek-Tips) but the memory still hasn't faded.

As far as I can tell, you have taken an excellent approach. With unique items, you can use VLookup or Match and it works just like the worksheet formulas. In VBA the command would be expressed as WorksheetFunction.Match(...) When the items are not unique, Find is the way to go.

As for what to do in general to make things go faster, see SkipVought's excellent FAQ: "How Can I Make My Code Run Faster" faq707-4105. In it he points out that using .Select is inherently slow and generally not needed in VBA macros. Applying the techniques identified there would probably have made your code fast enough for the purpose at hand. (But probably still not as fast as using .Find.)

I can sympathize with the difficulties in moving from procedural code like Basic to object-oriented programming like VBA. It's just one of those things that we all have struggled with until that breakthrough "aha" comes and it begins to make sense. Don't give up. I'm sure you will get there eventually. And here at Tek-Tips, you have a lot of friendly people who are more than willing to help you along the way.