CODEWITHSUNDEEP
vbaexcel
Prafulla Kumar Shahi
Prafulla Kumar Shahi

Reputation: 95

vba code not taking correct value of a cell from file

This is my code:

    Dim RowLast As Long
    Dim sunmLast As Long
    Dim tempLast As Long
    Dim filterCriteria As String
    Dim perporig As Workbook
    Dim x As String
    tempLast = ThisWorkbook.Sheets("combine BOMs").Cells(Rows.Count, "E").End(xlUp).Row
    Range("D5:G" & tempLast).ClearContents
    Range("G5:G" & tempLast).Interior.ColorIndex = 0
    tempLast = ThisWorkbook.Sheets("combine BOMs").Cells(Rows.Count, "A").End(xlUp).Row
    Range("A5:A" & tempLast).ClearContents
    tempLast = ThisWorkbook.Sheets("combine BOMs").Cells(Rows.Count, "B").End(xlUp).Row

'Perpetual
    Set perporig = Workbooks.Open("\\Etnfps02\vol1\DATA\Inventory\Daily tracking\perpetual.xlsx", UpdateLinks:=False, ReadOnly:=True)
    RowLast = perporig.Sheets("perpetual").Cells(Rows.Count, "A").End(xlUp).Row
    perporig.Sheets("perpetual").Cells(3, 1) = "Part Number"
    For i = 5 To tempLast
        Cells(i, 1) = i - 4
        perporig.Sheets("perpetual").AutoFilterMode = False
        filterCriteria = ThisWorkbook.Sheets("combine BOMs").Range("B" & i).Value
        perporig.Sheets("perpetual").Range("A3:J" & RowLast).AutoFilter Field:=1, Criteria1:=filterCriteria
        Counter = perporig.Sheets("perpetual").Cells(RowLast + 1, 1).End(xlUp).Row
        If Counter = 3 Then
            Cells(i, 5).Value = "Not on perpetual"
        Else
            ThisWorkbook.Sheets("combine BOMs").Cells(i, 5).Value = WorksheetFunction.Sum(perporig.Sheets("perpetual").Range("H4:H" & RowLast).SpecialCells(xlCellTypeVisible))
            x = perporig.Sheets("perpetual").Cells(Cells(RowLast + 1, 1).End(xlUp).Row, 4).Value
            MsgBox x, vbOKOnly, perporig.Sheets("perpetual").Cells(RowLast + 1, 1).End(xlUp).Row
            ThisWorkbook.Sheets("combine BOMs").Cells(i, 4).Value = x
        End If
        perporig.Sheets("perpetual").AutoFilterMode = False
    Next
    perporig.Close savechanges:=False

This is the file from which I am clicking my button (or ThisWorkbook)
enter image description here

This is the perpetual file when it is running on the last row of data:
enter image description here

Notice the difference in D9280: it shows stocking type as "P" in the perpetual file, but "B" in my final result, which comes up in cell D12 in ThisWorkbook. To debug, I created a Msgbox prompt for everytime it gets that value for all rows. For every other row, it gives the correct value ("P"), but for this one, msgbox shows "B". The title of the msgbox is the row number, which shows it is taking the correct row whilr getting the value, just that I don't know why it is taking wrong value. I have tried for different data sources, it seems to be coming up with "B" in wrong places every so often.

In the code, just above the line, I have the line to get the on hand quantity, which it does take correctly (I used xltypevisible to paste values for this field, but that is only because I wanted a sum of the results and this was the only way I knew). It's only this stocking type column which shows wrong values randomly.

Any ideas?
Thanks!

Upvotes: 1

Views: 1322

Answers (2)

Prafulla Kumar Shahi
Prafulla Kumar Shahi

Reputation: 95

Credits to findwindow, I found the answer. The .cells(cells()) part didn't have the correct sheet reference for the inner cells():

Instead of 

        x = perporig.Sheets("perpetual").Cells(Cells(RowLast + 1, 1).End(xlUp).Row, 4).Value
        MsgBox x, vbOKOnly, perporig.Sheets("perpetual").Cells(RowLast + 1, 1).End(xlUp).Row

I used this: 

            With perporig.Sheets("perpetual")
                x = .Cells(.Cells(RowLast + 1, 1).End(xlUp).Row, 4).Value
                MsgBox x, vbOKOnly, .Cells(RowLast + 1, 1).End(xlUp).Row
            End With

And it worked.
Thanks for your help!

Upvotes: 0

user3598756
user3598756

Reputation: 29421

1)

Cells(i, 1) = i - 4

as it is written , it refers to perporig.Cells(i, 1) is this what you want?

2) 

perporig.Sheets("perpetual").Range("A3:J" & RowLast).AutoFilter Field:=1, Criteria1:=filterCriteria

would filter from row 3, while you have headers in row 4 and data from row 5 downwards

change it to 

 perporig.Sheets("perpetual").Range("A4:J" & RowLast).AutoFilter Field:=1, Criteria1:=filterCriteria

3)

what do you think is Counter doing? Not certainly count visible rows only

Upvotes: 2

Related Questions