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

[PeterAtherton]

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" wrote:

> 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
>

[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

On Mon, 14 Mar 2005 19:47:01 -0800, "PeterAtherton"
<PeterAtherton@discussions.microsoft.com> wrote:

>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" wrote:
>
>> 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
>>

[Tom Ogilvy]

The formula should work. What is your question?

--
Regards,
Tom Ogilvy



"R" <ry@adelphia.net> wrote in message
news:stfc311rp2q8dr9bafem08m0vaf1cep35f@4ax.com...
> 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

[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

On Tue, 15 Mar 2005 08:10:39 -0500, "Tom Ogilvy" <twogilvy@msn.com>
wrote:

>The formula should work. What is your question?