Calculate values with

[Pastafarian]

Hi,

I have 2 sheets. Sheet 1 contains rows with orders. Each order has multiple products. Sheet 2 has the prices of those products, with in each column a different country (thus different price) and each row an other product.

For each order I'm trying to calculate the price for of all products combined, so I tried out to get the price of 1 product, but I'm doing something wrong.

This is the code that I'm using right now:

With Sheets("Sheet1")

    For i = 2 To AmountofOrders
    
    'On Error Resume Next
    
    .Cells(i, 25) = .Cells(i, 30) * WorksheetFunction.HLookup([B][SIZE="3"]Cells(i, 4)[/SIZE][/B], Worksheets("Sheet2").Range("AF2:AS50"), 2, False)
    
    
    Next i

End With

It basicly multiplies the amount of a product with the price (=value that needs to be looked up) and repeats that proces for all orders.


I read somewhere the highlighted part maybe won't work, but I can't figure out how to do it.
What is the best way to do this?


Thanks! ray:


From Holland with love,
Pastafarian

 

[Clay Canvas]

Pastafarian,

Your code as it's written works fine for me. It sounds like you may not want to use HLookup. By using HLookup your code is seeking a match for your search criteria in row 2. You mentioned your prices go across the columns by country and you have each different product in a new row on sheet 2. If you're looking for a match based on a product number/name from sheet1 and the products are listed in rows on sheet2 then you wouldn't want to use HLookup which will search across the columns. You could consider using VLookup.

Steve

 

[Pastafarian]

I already found it out. Its rather ashaming, but I forgot the dot in .Cells in the highlighted area. Thanks anyway!