Selecting a cell from filtered data within a macro

[excelloser]

Hi all Im looking for some expert help again

I have a set of userforms which working on the selected cell (a date), re-dates 4,8 or 12 weeks ahead, and records whether payment has been taken.

This works perfectly fine when I have selected the cell manually, but this needs running from a seperate sheet. My records are identified by a reference number in column U and replicated in column A. The reference number is entered into "lookup" sheet, cell "m2".

The code I have jumbled together appears to do the job. The record filters and the correct date cell is selected. Unfortunately when the weeks userform pops up, it refers to and alters data in the very first (now invisable) row of data and ignores the selected & visable row.

Hopefully my code will show an obvious error

Please Login or Register  to view this content.

From weeks userform

Please Login or Register  to view this content.

The correct cell is visibly selected when the weeks.show form is called, as it would be if Id clicked the cell myself no? But it acts as if the very first row of data (ref 1) is selected!


Once I know how to do this it will help with many other functions of my workbook

[Tinbendr]

Learn how to use range instead of select. I realize that the recorder uses Selection exclusively, but it has to.

I can't test these changes, but maybe you can see the logic.

Please Login or Register  to view this content.

[excelloser]

Learn how to use range instead of select. I realize that the recorder uses Selection exclusively, but it has to.

I can't test these changes, but maybe you can see the logic.

Please Login or Register  to view this content.

It doesn't seem to make a difference, other than the desired cell isnt selected at all. Why do you say to use range rather than .select? Reason I ask is my workbook code is full of both, often in the same macro.

I plan to learn everything I can, for now I have big tasks to complete without the right knowledge.

As long as the correct cell is selected, as if I had clicked with the mouse, the macro works perfectly. I cant seem to get code to do this tho.

If it helps at all, the macro is assigned to the double click of that cell, is that easy to replicate in a macro?

[excelloser]

Maybe the attached workbook can shed some light on what Im trying to achieve.


If I manually filter the record, manually select the date cell, and manually click the 're-diary' button on master, everything works fine. From the macro it doesn't. You will see the cell appears selected, but the weeks.userform which refers to activecell doesnt work.

Please review and advise

(Module 3) (button 3 on lookup)


example_rediary_lookup.xlsm

Please help


EDIT: If I pause the macro, deselect the cell and manually re-select it. The macro works.

When selected, it does look like its open for edit! I have tried cancel=true but didnt change anything

[excelloser]

Actually, looking at it, the correct cell seems selected but is tinted blue (meaning invisible data is selected?) if I click down, the correct cell is selected. Does this help?

[Tinbendr]

EDIT: If I pause the macro, deselect the cell and manually re-select it. The macro works.

That's why you use Range instead of Select.

Workbooks("MyWB").Worksheet(1).Range("A1") will ALWAYS equal THAT A1, regardless of what Selection is doing. When using Selection, you are at the mercy of what is selected. When you start bouncing around the sheet, problems like yours can pop up.

How is the lookup called? With the filter icon?

[excelloser]

ok, that makes sence

The filter button filters the selected record. The code we are discussing is assigned to the command button 3.



I understand my lack of knowledge frustrates when trying to offer help. I hope my workbooks show I do put a lot of effort in to trial and error before requesting help. This project has grabbed me, I will be learning starting with basics, but this project needs to be right asap.


Do you have any suggestions of how this might work? I just need to know how to work on the filtered data, how to refer to cells within the row.


Thanks again

[Tinbendr]

I've been looking at this for a while now and I'm starting to wonder, do you need to autofilter at all? If all you're doing is returning the number of weeks of occurrence and the account number, could you not just search for it and return it to the userform? I mean, it's nice, visually to see that you have that record, but is it necessary for the userform to function? And shouldn't this info be on your Lookup sheet anyway?

I'm just thinking outloud here, not trying to rewrite the whole thing.

You could add a Search sub that all the modules call to locate the row of the record and pull the info into our object.

Tell me what you think. Or the logic to the form.

[excelloser]

I've been looking at this for a while now and I'm starting to wonder, do you need to autofilter at all? If all you're doing is returning the number of weeks of occurrence and the account number, could you not just search for it and return it to the userform? I mean, it's nice, visually to see that you have that record, but is it necessary for the userform to function? And shouldn't this info be on your Lookup sheet anyway?

I'm just thinking outloud here, not trying to rewrite the whole thing.

You could add a Search sub that all the modules call to locate the row of the record and pull the info into our object.

Tell me what you think. Or the logic to the form.

You are 100% right! the reason Ive used autofilter is I dont know how to define activecell without knowing a row number, filtering that row alone felt logical. The buttons on the userform all reference activecell to get their start date. When I wrote the button code I hadnt thought ahead to this point, it was written to be run from a double click of that cell.

It sounds like you get what I wanna do. Thank you for the help.



PS. funny you should say re-write, because thats what Ive started doing. Ive stripped the datasets onto fresh worksheets. Gonna sort all formatting, data validation etc and then make a new lookup etc and try to write some clean code.

[Tinbendr]

You'll have to rename Sub Find() in Module1 to something else. It interferes with the VBA find.

Please Login or Register  to view this content.

[excelloser]

It does find the record and once the .activecell references are removed from the weeks userform_initialize sub it enters the correct value in textboxes. The code youve given me there alone will be invaluable

Everything from this point however doesnt work, Im guessing because it all refers to .activecell?
The weeks userform loads one of 3 userforms, which all look like this

Please Login or Register  to view this content.

Thinking out loud: If I want this to work, do I need to remove the code from the userforms and run from within macros loading the form first. I could then make two macros, one to use when cell is selected manually or double clicked, one for when called from the lookup sheet (both need to be available).
Or always hoping life can be simple... can I set the cell which .find found its data, as .activecell?





Again sorry for the ignorance.

[excelloser]

Attachment 167342 Changed a little since last upload

[excelloser]

Please Login or Register  to view this content.

Yay!

[Tinbendr]

Everything from this point however doesn't work, I'm guessing because it all refers to .activecell?

Just to get everything else working, add C.Select just before the Load Weeks.

I can't quite figure out how your code works, so you may have to use select until you can get it converted.

Thinking out loud: If I want this to work, do I need to remove the code...

Yes, eventually.

As your skill improves, so will your technique.

I'll be glad to continue to help you, but you'll have to write me a step-by-step on how the whole thing works so I can get my head around it. PM me if you like.

[excelloser]

What a legend




As you can see I have chosen bad technique for the short term fix.... but its working!

I have had to remove attachment, had left a little sensitive data in there (doh!) will reupload.

things are starting to click, I think the whole thing wants re-writing soon! its a great project