VBA that looks up for a place in table and then write data

[pierrejof]

Hi all,

First off, I am sorry for the title that is probably not clear. I have recently started to understand what VBA does and why it can be powerful. However, I have never learnt programming, and I find it a bit difficult to suss out. I would therefore be really grateful if someone could help on this.

I have indeed looked up for solutions in tutorials, books etc. and i have not come across a solution for this:

What I would like is basically a code that makes a workbook to run a sort of log book, or similarly behaves like an inventory.

I would need a bit of code, that looks up in table using an ID #, and write data in the right cell.
The purpose of this is for consulting and UPDATING matters in order for users to access information based on an id, and also update the information from a dashboard rather than going to the database.

I find it a bit difficult to explain... I hope the document I have attached will make sense.
The code I have written so far is just what a classic vlook up would do, but since i need to update the fields, I need these to contain my content and not the formulas. (Hence i need VBA)

I have put some more details in the spreadsheet.

I would be extremely grateful if somebody could give me a hand on the code, point out a tutorial / forum post etc.

Cheers,

pierre

[nathansav]

Hi
You need to use application.worksheetfunction.match(lookup value, range to look in,false) this will give you the row number

then cells (row, column) = data

My VBA is not working at the moment, computer needs rebuilding, so i cant mock up, if i can i will soon.

[pierrejof]

Hi Nathan,

Thanks very much for your message. Let me try it. I'll get back to you if I struggle.

Cheers!

pierre

[pierrejof]

Hi Nathan,

I didn't manage to make it work...

Here's the code:

x is the cell where the user inputs id number
Range(A2, C9) is where the database is ( including id number)
Sheet2.Range(D7) is where i'd like the value to be copied

Please Login or Register  to view this content.

It returns an error message that says "assignment to constant not permitted".

can please you advise ?

cheers,

[pierrejof]

Hi Nathan,

Have you got a bit of time for me?

I still haven't found how to make it work.

[pierrejof]

Anyone else ?

[nathansav]

Application.WorksheetFunction.Match(x, Sheet2.Range(A2, C9), False) = n


Wrong way round

should be

n= Application.WorksheetFunction.Match(x, Sheet2.Range(A2, C9), False)

[pierrejof]

Hi Nathan,

This is now working!! Thank you very much for your help.


Cheers

[nathansav]

no worries, had some time off so sorry took a while to get back.