unknown Application-defined or object-defined error

Question

i copied a working code and changed it a little bit to fit the current table but it gives me the application defined error.

Basically the code goes through a list of data, make sure that 2 labels (state and then label A) matches and increase the counter by 1.

From what i have seen, the error appears when state matches but label A does not match.

Sub SPENDING_update()
STORE_CODE = Application.Match("STORE_CODE", Sheets("BaseData(ClientVersion)").Range("1:1"), False)
Spending = Application.Match("SPENDING", Sheets("BaseData(ClientVersion)").Range("1:1"), False)
STORE_TYPE = Application.Match("STORE_TYPE", Sheets("BaseData(ClientVersion)").Range("1:1"), False)
WEIGHTING = Application.Match("WEIGHTING", Sheets("BaseData(ClientVersion)").Range("1:1"), False)

Lastrow = Sheets("BaseData(ClientVersion)").Range("A" & Rows.Count).End(xlUp).Row
table1ref = Application.Match(2, Sheets("Extras").Range("A:A"), False)
counter = 0

Sheets("Extras").Activate
Sheets("Extras").Cells(table1ref, 1).Offset(2, 2).Select

For j = 3 To 6
    Do Until ActiveCell.Offset(0, 2 - j) = "Total"
        For i = 2 To Lastrow
            Select Case Val(Left(Sheets("BaseData(ClientVersion)").Cells(i, STORE_CODE), 1))
                Case Is = 1: state = "VIC"
                Case Is = 2: state = "NSW & ACT"
                Case Is = 3: state = "WA"
                Case Is = 4: state = "QLD"
            End Select


            If state = Sheets("Extras").Cells(table1ref, 1).Offset(1, j - 1) Then
                If ActiveCell.Offset(0, 2 - j) = Sheets("BaseData(ClientVersion)").Cells(i, Spending) Then
                    k = Sheets("BaseData(ClientVersion)").Cells(i, WEIGHTING)
                    counter = counter + (1 * k)
                End If
            End If
        Next i
        ActiveCell = counter
        counter = 0
    ActiveCell.Offset(1, 0).Select
    Loop
    Sheets("Extras").Cells(table1ref, 1).Offset(2, j).Select
Next j

j = 7
Sheets("Extras").Cells(table1ref, 1).Offset(2, 6).Select

Do Until ActiveCell.Offset(0, 2 - j) = "Total"
    For i = 2 To Lastrow
        state = Val(Left(Sheets("BaseData(ClientVersion)").Cells(i, STORE_CODE), 1))
        If state = 1 Or state = 2 Or state = 4 Then
            If Sheets("BaseData(ClientVersion)").Cells(i, STORE_TYPE).Value = "Corporate Store" Then
                If ActiveCell.Offset(0, 2 - j) = Sheets("BaseData(ClientVersion)").Cells(i, LATEST_PROD) Then
                    counter = counter + 1
                End If
            End If
        End If
    Next i
    ActiveCell = counter
    counter = 0
    ActiveCell.Offset(1, 0).Select
Loop


End Sub




+---+-----------+-----+-----+----+----------+
|   | NSW & ACT | QLD | VIC | WA | COHO ESB |
+---+-----------+-----+-----+----+----------+
| A |           |     |     |    |          |
| B |           |     |     |    |          |
| C |           |     |     |    |          |
| D |           |     |     |    |          |
| E |           |     |     |    |          |
+---+-----------+-----+-----+----+----------+

The code that was highlighted by VBA is "If ActiveCell.Offset(0, 2 - j) = Sheets("BaseData(ClientVersion)").Cells(i, Spending) Then" This code is right after the select Case.

I have included what the table which is used for matching looks like. the expected result should be counter increase by 1 if both state and label matches and then once the loop finished at lastrow, it paste the counter value in the table, reset the counter back to 0 and move on the next state and label.

Thanks

`2 - j` offsets to the left... Is that what you want? Depending on what the `ActiveCell` is (which btw it's best to avoid using `ActiveCell`), you might run out of columns to offset to. — BigBen, May 03 '19 at 02:41
@bigBen the active cell starts at row A, column "NSW & ACT". offsets to the left is to match the data in another sheet to that value, in this case - "A". If the data matches "NSW & ACT" and "A", the count goes up by 1. Once the whole column of data has been checked, the final counter is pasted at the active cell. Once pasted, the counter is reset to 0 and it move to the next cell. — Chan KhangYi, May 03 '19 at 02:53
If the activecell is in column A, you *can't* offset to the left. `2 - j` equals `-1`, `-2`, etc. Did you mean. `j - 2`, which would offset to the right? — BigBen, May 03 '19 at 02:53
@bigBen the offset to the left is to find the label. -1, -2 is how i want it to move to the left to find the label. So in this instance, if my active cell is at column 3, and my label is at column 2 so i want it to offset to the left to look for the correct label. If my active cell is at column 5, the label is still at column 2, so 2 - 5 =-3. 3 cells to the left is the label cell (column 2). j loop will be moving the active cell, not the offset formula. — Chan KhangYi, May 03 '19 at 03:22
If I can suggest debugging this, add a `Debug.Print ActiveCell.Column` and `Debug.Print 2 - j` instead of trying to offset. What are the results? Also, what does `spending` equal? — BigBen, May 03 '19 at 03:25
Also, if I can suggest a read of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, May 03 '19 at 03:35
@bigBen as shown in the code. Spending has been assigned to the Column with the spending data. May i ask where do i add the debug code? As for the select, i am not quite sure what other ways i can use to input data into a table using vba. Thanks. Are there any ways i could share my file so that people can understand more clearly on how my code works? — Chan KhangYi, May 03 '19 at 03:47
Comment out the `If ActiveCell.Offset(0, 2 - j)...` and instead add the two `Debug.Print` lines. — BigBen, May 03 '19 at 03:50
@bigBen so what exactly am i looking for with the result in debug? — Chan KhangYi, May 03 '19 at 03:53
Spending is a list of data it could be "$0 - $4.99" or "$5 - $9.99" and so forth — Chan KhangYi, May 03 '19 at 04:12
So i have copied the exact code into another workbook and everything works. it just doesnt seems to work in this particular workbook. Almost 100% sure that the code is fine, but i have no idea which part of the workbook/ Table is going wrong — Chan KhangYi, May 03 '19 at 04:14
The smallest legal column is `1`: you can't offset into negatives, that's why you're getting error 1004. As @BigBen alluded to, being more explicit about what cells you refer to and avoiding to depend on `ActiveCell` is going to fix that. — Mathieu Guindon, May 03 '19 at 04:31
I am quite a new VBA user, does anyone has a way of looping this without me specifying the exact cells? there are 20 cells, so i dont really want to have a too code that is too messy. thanks. Basically in the dataset, there will be a column for Store code, and spending. I will need to categorize the storecode into state, and making sure the spending matches one out of the 5 possible data before adding to the count. There will be 5 answers for the spending and 4 states to be categorize into. thanks — Chan KhangYi, May 03 '19 at 04:36
To be honest, that's quite a lot of code, and all these `Offset` calls are making it rather hard to follow, especially since sometimes you explicitly qualify `Range` (/`Cells`) calls with a `Worksheet` reference, and other times you qualify with `ActiveSheet`, but then the active sheet is the same one that's qualified... it gets very confusing and hard to see what the *intent* (and therefore the bug) is. Declare a `Worksheet` variable for each sheet that's involved, *and use it* to qualify every single `Cells` or `Range` call that you have; the cognitive load is very large in this procedure. — Mathieu Guindon, May 03 '19 at 04:45
Is your table a `ListObject` (i.e. "format as table", with a table name, and table formulas)? If so, you could dramatically simplify the logic and completely remove the offsets. — Mathieu Guindon, May 03 '19 at 04:50
No, they are not formatted as table, just a range of cells coloured into a table. they are identical to the one you see in the code. — Chan KhangYi, May 03 '19 at 04:55

unknown Application-defined or object-defined error

0 Answers0