Add to lookup table from user completed form

[tony7262]

Hi,

I need to create a macro that will take a form that a user fills in and add the information to a pre existing lookup table.

Attached is an example of what I am looking for and the basics are I would like the macro to use the Grouping to determine which column to add the category to (these are just the names I have used in the example) and then take the new category and replace the first time that the word 'Spare' appears in the lookup list.

I can get all the basics of finding the correct column/row etc just can't work out how to join it up and fill all the data in from the user completed form.

Thanks in advance,
Tony

[HaHoBe]

Hi, Tony,

maybe the following code could work for you (code goes into a standrad module for me):

Please Login or Register  to view this content.

Ciao,
Holger

[tony7262]

Hi HaHoBe thank you this actually works better than I was hoping for - means I don't need to have the word 'Spare' all over the place I can just have the lists

[tony7262]

Hi,

I have been looking at this again and now decided it would be better if the form part was on a different tab to the lookup table (so I can lock the lookup table away and stop users changing things in there directly)

I can't seem to get it to work across two sheets - I've got to here in the attached but if you can help that would be greatly appreciated.

Dim var
With Sheets("Sheet1")
var = Application.Match(Range("G7"), Range.("D4", "F4"), 0)
If Not IsError(var) Then
Cells(Rows.Count, var).End(xlUp).Offset(1, 0).Value = Range("G8").Value
Range("G7:G8").ClearContents
Else
MsgBox "no match found"
End If
End Sub

[HaHoBe]

Hi, Tony,

could you please wrap the procedure with Code-Tags?

Ciao,
Holger

[tony7262]

Hopefully that worked....

[HaHoBe]

Hi, Tony,

I didnīt mean to double up the code but alter it in posting #4.

Please Login or Register  to view this content.

The caveat with Match that itīs delivering the number of the column/row found within a range, if your code just reflects on a given range you must adjust that number of columns/rows with the number of blanks/written before that to get the correct number to add to.

Ciao,
Holger

[tony7262]

Hi HaHoBe,

Thanks again this does do what I was looking for - I have one more variable which I would like to account for...

If I wanted to also add into Row 9 of Sheet2 a Yes/No box and then only have the macro run if everything else from the code worked but also that G9 said "Yes" can that be added as well? If it said "No" it would just not run.

Thanks again for your help - stopped me going round in circles with this!
Tony

[HaHoBe]

Hi, Tony,

just add that range to the If-Statement like

Please Login or Register  to view this content.

Ciao,
Holger

[tony7262]

Hi Holger,

Thanks for your help with this - I have edited everything to match to the 'live' file and the code is as follows:

I am having one final problem - it works perfectly if I select 'G&A' in D23 but if I select 'Selling' I get the "Error - Contact File Creator" message and I cannot work out why.

Any chance you could troubleshoot the code? - ps it doesn't match to the example I posted earlier

Thanks,
Tony

[HaHoBe]

Hi, Tony

the command UCase changes the string to all uppercase. In your code you compare the upper-case SELLING to Selling which should not match.

You will need to add the logic for a third sheet into the code as that wasnīt planned by me.

Please Login or Register  to view this content.

Ciao,
Holger

[tony7262]

Thanks Holger - works perfectly now