If ElseIf Else Statement not working

u have wrongly define the Last row

Please Login or Register  to view this content.

that is what its going in GHI

Just replace your Row finding code with below one

In ABC

Please Login or Register  to view this content.

In DEF

Please Login or Register  to view this content.

In GHI

Please Login or Register  to view this content.

The problem is this:

Please Login or Register  to view this content.

That is the equivalent of saying go to the bottom of column E and press the End key and the Up Arrow key.

Unfortunately, that takes you to the area related to outlet GHI, regardless of what outlet you select in the drop down combo box.

You need to modify the way that you locate the relevant row, something like the code below:

Please Login or Register  to view this content.

See the attached example.

Regards, TMS

The form specifies a Week Number so, presumably, it's not just a case of going up to the next available blank row for an outlet, otherwise there would be little point specifying a week number and listing the weeks down column A

Hence the reason that I have used:

Please Login or Register  to view this content.

and, later:

Please Login or Register  to view this content.

Using separate row numbers for each outlet means the code is, of necessity, repetitive and redundant. I haven't changed it, but, instead of hard coding the store in the MATCH, you could use the input store. You could then replace the nr3, nr4, nr5 and nr6 and remove two thirds of the code.


Regards, TMS

Money where mouth is ...

Please Login or Register  to view this content.

Thank you for your kind comments in your Private Messages.

If these suggestions have resolved your issue, please mark this thread as solved.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Please don't quote entire posts. It is unnecessary and just clutters the thread.

However, my current setup required the week column value that Enter by the User therefore, the code you provide couldn't work.

The code, as provided (by me) in both versions does work. It uses the Week Number entered into the box in the User Form to determine which rows to update. Other responses just addressed the fact that using End(xlUp) would not necessarily get you to where you wanted to be, and provided workarounds.

How else would the user enter a Week Number? And what is the purpose of having the box on the User Form if you do not use it to match the week number in the worksheet?

Have you checked and tested the updated workbooks that I uploaded? Note that the second one is about 3kb smaller from removing the redundant code.

Personally, I probably wouldn't have separate areas for each Outlet. I'd just have an extra column and write the Outlet Code there. Similarly, use the Week Number entered in the User Form in a separate column instead of pre-coding the entries. Then you can just sort and filter the data by Outlet and Week Number.

But, in practical terms, this isn't really the place to design your workbook/worksheet/application on the fly.

Regards, TMS

I fixed the date problem and amend the whole file according to your suggestion, therefore I have this error code in debug mode

Please Login or Register  to view this content.

You may refer attached file to see the changes.

Thank you so much.

Edit: I notice if my Week column empty which causing this problem, may I know how to solve it?

The code:

Please Login or Register  to view this content.

Report-Test-v1.xlsm

OK, that wasn't quite what I had in mind My intention/expectation is that there are NO ENTRIES at all for Outlet and Week Number, and they are completed from the User Form.

Please see the attached updated example. I have cleared the "raw data" input sheet. Just complete the form for each Outlet/Week and Save the data.

Regards, TMS