Cannot make .Offset to work in excel vba macro

Question

I am stuck, and would like a little hlep. I'm trying to get a qty on Column D12, d14, d16 .... to d42. Than multiply this qty by a value. the value sheet look like this.

Value Sheet

         A        B          C
       ItemName   Quality   Confort
  1     Chair     2          1
  2     Bed       0          3
  3     Table     1          1

Quantity Sheet

      A      B         C      D
            ItemName        QTYColumn
12    ..    Table             2
13 
14    ..    Chair             5 
15          
16    ..    Bed               6

Total Sheet

    A         B    
  Quality    12    (2*1 + 5*2 + 6*0 )
  Confort    25    (2*1 + 5*1 + 6*3 )

I'm pretty sur I have the hardest part done. I can check and grab the quantity from all the sheets I want. I also got a function done where you pass the name of the item, and the stats name, and it return me the results I want.

so, I got this part of the code atm which doesnt work, and its driving me nuts.

 For Counter = 12 To 42 Step 2
     For Each qColumn In QTYColumn

       Set QTY = Range(qColumn & Counter)
       Dim ItemName As Range
       ItemName= QTY.Offset(-2, 0).Select

       total = total + (QTY * GetValue(ItemName, "Confort"))
    Next qColumn 
Next Counter

My problem is with the ItemName variable. Its always empty and as soon as I get to it with the debugger, the function stops and it closes. Anyone have any idea as to why ? it's important for me to get it base on the offset -2 and not the column adress because it might be different depending of the sheet, and the only "sure" way to find it is the get the 2nd cell to the left of the quantity cell.

well, I'm going to eat at my parents. I'll be back in a couple of hours and answer any questions or stuff I might have written "wrong" — Thierry Savard Saucier, Oct 12 '13 at 20:41
Second cell to the left should be `QTY.Offset(0, -2).Select`? — Ioannis, Oct 12 '13 at 20:42
Put `On Error Goto ErrHandler` at the top, and add `ErrHandler: If Err.Number <> 0 Then Stop` at the bottom. A function that just exits looks like a function that's failing, you need to get `Err.Description` to get a clue. Probably your range address is invalid. — Mathieu Guindon, Oct 12 '13 at 20:44
as for the .select, I'm not sure. I've been trying for a while and mix up a bunch of exemple. I think it was to switch the active cell ... but I cant recall — Thierry Savard Saucier, Oct 13 '13 at 00:26
aaa loannis thanks ! Dont know why those 2 offset parameters are this way, it seems like they are inverted in my mind, but at least now I'm getting the name I wanted — Thierry Savard Saucier, Oct 13 '13 at 00:29
[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Oct 13 '13 at 06:22

score 2 · Accepted Answer · answered Oct 12 '13 at 20:56

2

ItemName= QTY.Offset(-2, 0).Select does not mean anything !

Either you Select:
QTY.Offset(-2, 0).Select

or you get the value:
ItemName= QTY.Offset(-2, 0).Value '(value can be omitted here)
But then, Dim ItemName As Range does not make sense. It should be a String or a a number.

or you get the range:
Set ItemName= QTY.Offset(-2, 0) ' then you need Set

answered Oct 12 '13 at 20:56

iDevlop

24,841
11
90
149

excellent, with this and loannis comments I got it to work finally ! – Thierry Savard Saucier Oct 13 '13 at 00:34

Cannot make .Offset to work in excel vba macro

Value Sheet

Quantity Sheet

Total Sheet

1 Answers1