Update Existing Table with UserForm Values

Hi again,

We don't use "SET" with regular variables (such as Strings, Integers, etc.), but only for objects (Workbook, Worksheet, Range, etc).

Please Login or Register  to view this content.

should just read

Please Login or Register  to view this content.

New Problem,

Now when I run the UserForm I get a RunTime Error 13: Type Mismatch in the Change procedure for the ComboBox:

Please Login or Register  to view this content.

I tried changing the code so it did not rely on the Dynamic Named Range Lst_Locations, but now I get a Runtime Error: 1004; Method 'Range' of Object '_Worksheet' Failed:

Please Login or Register  to view this content.

Sorry I didn't reply earlier, glad it works for you

Please remember to mark the thread as solved? Thanks!

Oh no sorry, didn't see the new post

Can you upload a workbook?
The second code you tried... have you given "lr" a value? It is an error that would happen (among other things), if lr was 0. It could also help to put ws. in front of "cells".

Byt the way:

Please Login or Register  to view this content.

errors because LocEdit is an array, get rid of ".Value" at the end and it would work fine.

Thanks. I added "ws." in front of Cells in the Change procedure from eariler. The Error went back to Run Time Error 13: Type Mismatch

Please Login or Register  to view this content.

Ok, I see the problem... in your first version you use a named range, which is just column A, but your Vlookup asks to return the value in the second (or thrird, etc) column of the vlookup range.
The same problem in your second version, but additionally, because the Locations sheet is not active, you have to put ws in front of cells when specifying a range (otherwise the active sheet is implied):

Please Login or Register  to view this content.

should be something like this:

Please Login or Register  to view this content.

I put the changes in red in the first line, but as you can see all lines need to change. Your newer version:

ws.Range("A2", ws.Cells(lr, 1)), 2, False)

won't work either, you would need ws.Range(ws.Range("A2"), ws.Cells(lr, 5

Please Login or Register  to view this content.

)), 2, False)

Truth is that you could make that line lr,2 then the next line that wants the third column can be lr,3, and so on, but you need a vlookup range with at least as many columns as the column number you want the value for.

I'm still getting a Run Time Error 13:

I tried your updated code with "ws." in front of all the cell references. I also tried creating a dynamic range "refRange" but I get the same error in the same place.

Please Login or Register  to view this content.

The error occurs after I've loaded the user form, edited values in the Edit Locations tab and clicked "Save and Edit Next".

I really appreciate the help!!

Hi TFiske,

Please fix post #12 with code tags.

Thanks for fixing that....

Your problem is that once you blank the location after saving, the change code is triggered and the vlookup fails because the textboxes are blank.

You may want to put

If CmbBox_LocationEditSelect.Value = "" Then Exit Sub into the CmbBox_LocationEditSelect_Change() code:

Please Login or Register  to view this content.

Originally Posted by TFiske

Apologies. Fixed.

Appreciate it...

Please Login or Register  to view this content.

@bakerman2, showoff!

Lol just kidding of course, good idea

Glad we could help

@ Arkadi

Do you have a problem with me ?

Also for performance better 1 Match then 4 Lookups.

bakerman2, I was joking of course my friend... I thought your solution was very nice!

@ Arkadi

Nothing wrong with having some fun once and a while.

@TFiske

You're welcome and thanks for rep+.