Question on Adding a specific condition to a macro

[mjp155]

Hello,

So I have this macro that filters entered data by quantity. There are hundreds of items, and for selected items I fill in the quantity and price (one is in column E, the other in Column F), and I use the macro to filter what I fill in by quantity, so all I can see is the data I have entered in. I want to prevent a situation where I fill in data for Exx but not Fxx (xx=the row # containing the item I am filling in data for).

What VBA code is there where I can say "If I Fill in Exx but not Fxx or if I fill in Fxx but not Exx, a message box pops up...otherwise continue with the macro".

Any thoughts?

 

[Joe4]

Can you post your macro code, and we'll see if we can help you modify it to do what you want?

 

[mjp155]

Here is an example of the code I am using:

Columns("I:M").Select
Selection.EntireColumn.Hidden = False

ActiveWindow.ScrollColumn = 2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=8
ActiveWindow.SmallScroll Down:=78

Selection.AutoFilter Field:=4, Criteria1:=">0", Operator:=xlAnd


ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("E30").Select
ActiveWindow.SmallScroll Down:=9
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1

Columns("I:M").Select
Selection.EntireColumn.Hidden = True

 

[Joe4]

How is this macro being called?
How does it know which row to look at column E and F for? Is it always the current/active row?

 

[mjp155]

the rows that the user can use to fill in data on this worksheet can be anywhere from row 29-640, depending on the item they want to fill in data for. The user can use as many rows as needed (example: user could fill in row 36,78,45,560,600, and row 629)...the macro code I gave you is so the user can show just what they have filled out. They need to put info in both column e and f for a particular row if they choose to put in data for an item....I want the user to be alerted if they fail to do that.

 

[Joe4]

They need to put info in both column e and f for a particular row if they choose to put in data for an item....I want the user to be alerted if they fail to do that.

That is what I am driving at.
How do I determine which rows to check to see if columns E and F have been populated?

To put it another way, from your original post, you said:

I want to prevent a situation where I fill in data for Exx but not Fxx (xx=the row # containing the item I am filling in data for).

How do I determine what "xx" is?
What is the logic for determining this?<!-- / message -->

 

[mjp155]

They can fill out anywhere between E29-F640. If I run the macro I gave you...it can filter on quantity (column E ) but not necessarily on the total number data they fill in (Column F). So it is possible that when they run the macro, some items they want to show up may not appear if they fill in Column F but not E. (They also NEED to fill out both fields for this excel form to be accepted).

They choose what they want to fill out info on, and this will be different every time this form is filled out.

 

[Joe4]

I don't think you are understanding what I am trying to ask you.

The macro needs some way of determining what row it is looking at to determine if criteria has been entered into columns E and F. It can be dynamic, but we need to tell the macro how to do that!

What if I told you that we can tell the macro to check cells E30 and F30 every time it ran to see if there are entries in both cells?

You would probably come back and say that won't work because they won't be entering the data in row 30 every time.

To which I would reply, how do we know what row to use then?

That is why I was asking how this macro is being called/run. Is there a button they are clicking, or are they just going to the Macros menu and selecting it and running it?

If they are using the menu, can we just tell the macro to use whatever the current active row is (if they click the button right after typing in the data, the cursor should be in the correct row and we can "capture" the row that way).

If this is not making sense, try posting an actual sample, explaining step by step how they would run this and what cells they are in at each step along the way.

 

[mjp155]

The user is clicking a designated button to run the macro.

There are many different users who use the spreadsheet...they can potentially fill in data for many many items, which would require them to fill in data for Column E and F. Therefore, it is possible that there could be many items within their sheet that have E/F filled out but not the other. Let me also note that there is also another macro for them to filter the many items by category, lets call this MACRO A. Lets call the MACRO I talked about earlier MACRO B.

Example of what they would do:

a user has 10 items they want to fill out info for: quantity of how many items they want (Column E), and how much they would pay for it (Column F). They use one MACRO A which filters by category so they can narrow down the items. Then they fill out, lets say E45 and F 45, then E67 and F 67. Then user the filters for another category, and fills out E32/F32 and E333/F333....this goes on until they are done with the items they want to fill out. At the very end, of the hundreds of items in the list, they want to just show the items they have filled out info for, which is MACRO B.

I am looking for a way(when they hit the MACRO B button) to alert the user when they have failed to fill out both E and F fields for the items they entered any data for.

Does that make sense?

 

[Joe4]

OK, now I think it may be beginning to make a little more sense. Let me see if I go this correct.

So when a filter is applied, it returns all the records they need to populate. If they wish to run another filter so they can make more entries, your first want to verify that they completed all the previous entries before moving on to the next filter. Am I on the right track?

If so, then it sounds like when they click the Filter macro button, you first want it to verify that columns E-F have been populated for all VISIBLE rows. Is that right?

If so, that just leads to one more question. When the first open the workbook, it is going to have any filters applied, or show all data? Because if it shows any data where records E and F are not populated it is going to be a bit more tricky to make sure that the first filter attempts doesn't fail, saying that there is some unpopulated data.