Auto Select - Drop Down Option

[shanenavy26]

Here is the Sample that I will be referencing: https://www.dropbox.com/s/qsnzojulgipugma/Test.xlsm?dl=0

In this financial planner, I have data in sheet one that gets moved over to sheet 2. When this happens, it auto updates the text to remove all the repeating data. (card number). I have made rest of the process automatic except for needing to use the drop down list I created and choose a category for each expense. You can see that in the C- Column.

**Please note, you have to copy and "PasteAsValue" Otherwise the categories don't work. (unless you know a fix for that too).**

My question is: There are many expenses reoccurring every month, such as the same restaurant, or same store, and bills.
how can I get specific strings to auto choose a category. Such as in the Sample, I made "TACOBELL" and "WALMART". When I paste over the expenses, id like a VBA or some other method to be able to find taco bell, and auto choose the Fast Food category, and for Walmart to auto choose the store category.

I would also like the ability to add and edit these strings so I can filter more of these expenses later. Thank you!!

 

[shanenavy26]

https://www.dropbox.com/s/6c5cun80l9fqk1i/BIG TEST 2.xlsm?dl=0

Im sure i'm doing something wrong with the table, but mine looks like how you wrote it. Here is what i got right now.

 

[Fluff]

That file works fine for me.
Do you have any other event code in your workbook?
If so add this line as shown

   If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
   [COLOR=#0000ff]Stop[/COLOR]
   Application.EnableEvents = False

When you paste the data into the sheet, the VBE should open with that line highlighted.
Once that happens use F8 to step through the code. Do you always stay in that code, or does the highlighted line jump to any other code?

 

[shanenavy26]

That last link is what I have open right now. I only have that code for 1 sheet, no other event codes in the workbook.
When i try to paste the "If Interset" code i get an error. and when using F8 through the code I have in place now it does everything perfectly, just getting some categories wrong.

https://www.mrexcel.com/forum/redir...om/s/6c5cun80l9fqk1i/BIG%20TEST%202.xlsm?dl=0

Use this link for what im seeing.

 

[Fluff]

With that workbook I select sheet1 > select the range to copy > Ctrl C > select sheet March > select A3 > Ctrl V.
Everything seems to work correctly & I end up with what I showed in Post#10

 

[shanenavy26]

I apologies for having so much trouble with this.

Here is what im doing step by step:

1) Download file from Post #13
2) Copy A1:E54 From "Sheet1"
3) Paste into A2 of "March"

Outcome: The strings replaced like supposed too. Categories Auto selected from Table, however everything that should be blank, is now "Groceries" as well. Also the category colors didnt change everything is white.
NOTE: The problem with just paste, is it gets rid of the drop down option from the cells.

SECOND TIME
1) Download file from Post #13
2) Copy A1:E54 From "Sheet1"
3) PasteAsValue into A2 of "March"

Outcome: Similar result as the first one, however the color worked this time, assuming because the drop down lists are still there.
Any idea why im getting such drastic outcomes compared to you?

 

[Fluff]

Any idea why im getting such drastic outcomes compared to you?

Unfortunately, none whatsoever.
When I do a straight paste (Ctrl V) the dropdown boxes are still there & so is the CF.

I did notice that some of your CF formula have become fractured & don't work on some rows

 

[shanenavy26]

I noticed the broken cells, i just had to paste over the formatting.

Is there setting in excel that I have different than you that are creating the differences in outcome?

 

[shanenavy26]

Update: There seems to be a disconnect between Excel and WPS. As you are correct it works perfectly in excel.

 

[shanenavy26]

Update: I have figured out the problem. When using the table U2:V8: It will replace what is supposed to be blanks, with whatever is in U2:V2.

My solution was to move everything down, leaving U2 blank. Then everything worked perfect. Not sure why it was doing that, but at least it's fixed now.

 

[Fluff]

Glad you sorted it & thanks for the feedback