Using VBA to auto populate cells based on user input (without using formulas if possible)

[guyinahatgames]

Hello

Windows Server 2005
Excel 2003

This is based on the same spreadsheet I used in another thread post. I'm trying to create a spreadsheet where somebody who's computer illitarate can type a product code in column B and it'll auto populate column F and G all done in VBA without using forumlas within the spreadsheet itself.

Reason being is that I'm under a strict file size limit and have to keep it as small as possible so having a forumla in one cell and dragging it down isn't the best option sorry.

I tried to create a VBA code which would auto populate the cells by placing the forumla into the cells when needed meaning I don't have to have 4000 rows of formulas sitting there waiting to be used (if that makes sense) but I couldn't get it to work it just returns a value of 0 Also it only works on the one cell not he cells adjasent to each other (sorry I'm explaining that poorly)

Also this process has to be done automatically without anybody resorting to macro buttons. Of course if anybody can come up with a method of doing this without inputting any forumals at all that'll be grand :D

Any help would be very apprechiated thank you

Please Login or Register  to view this content.

[humdingaling]

Please Login or Register  to view this content.

slight amendment

[guyinahatgames]

Sir thank you very much for your help that's exactly what I needed :D

Mind if I ask what '.Address' is please I've never seen it before I'm quite new to VBA?

Again thanks much apprechiated :D

[humdingaling]

.address gives the cell reference

ie
if target is row 54
Cells(Target.Row, 2) returns cell(5,2) = $B$54