How to format a cell with VBA to return a value from a database?

[R]

I need to write a program for construction estimating for my company.
Nothing fancy, justa little something to make my life easier. Since I
never know how many items I will need to lookup, I want to have the
userform update the excel cells as needed.
Here is the code I am trying to get to work. It looks at the previous
cell in the row for a match description. If it finds one, I need it to
lookup the value in the pricing database and return the value in the
cell.

=IF($K18="","",VLOOKUP($K18,'C:\Templates\pricing.xls'!OIGDB,3,FALSE))

example
Lets say in the description cell it finds "30 year shingles"
It then goes to the pricing database and findes "square foot" for
column 3 so will show "square foot" in the cell.
I hope this makes sense.

Thank you for any help.
Rob

 

[Guest]

Rob

Do you need VB? I think you could use an advanced filter with your lookup
formula as a criterion.

Try the Help on filters and try debra dalgliesh's site for great tips on
filters.

Regards
Peter

 

[R]

Peter,
I do have VB. It has been quite a while since I did any programming
and have never done any programming for excel. I will search for
Debra's site and see if there is anything to help me there.
Thanks,
Rob

 

[Tom Ogilvy]

The formula should work. What is your question?

 

[R]

Here is the code I have once the command button is clicked:

Private Sub CommandButton1_Click()
ActiveCell.Value = UserForm1.ListBox1.Value
ActiveCell.Offset(0, 2).Select
ActiveCell.Formula =
"=IF($K21="","",VLOOKUP($K21,'C:\Templates\pricing.xls'!OIGDB,3,FALSE))"
End Sub

It still will not work. Not sure if it is a pathway error or using the
wrong formula attributes?

Rob