dimanche 29 juillet 2018

How can I get a right order of data from previous sheets with VBA

Could someone help me out with the following code, I thought i figured it out but keep on stranding with the same problem:

Sub history()
nsheets = ActiveWorkbook.Worksheets.Count 'count sheets in workbook
nas_index = ActiveSheet.Index 'index of the activated sheet
nas_LR = Sheets(nas_index).Cells(Sheets(nas_index).Rows.Count, "A").End(xlUp).Row 'count rows of activesheet

For d = 1 To nsheets
    If d < nas_index Then
        pre_index = Sheets(nas_index - d).Index
        pre_LR = Sheets(pre_index).Cells(Sheets(pre_index).Rows.Count, "A").End(xlUp).Row

        oldtime = Sheets(d).Cells(1, 6).Value
        newwknr = Sheets(nas_index).Cells(1, 7).Value
        oldwknr = Sheets(pre_index).Cells(1, 7).Value
        StrOldTime = Format(oldtime, "hh:mm:ss")

        For n = 3 To nas_LR
         prid_new = Sheets(nas_index).Cells(n, 1).Value

         For o = 3 To pre_LR
            prid_old = Sheets(pre_index).Cells(o, 1).Value
            pre_am = Sheets(pre_index).Cells(o, 6).Value
            pre_amw = CStr(pre_am) & "(" & StrOldTime & ")" & "(wk: " & oldwknr & ")"
            If prid_new = prid_old Then
             'Below is not working properly
             '------------------------------
                re = re & " " & pre_amw 
                Sheets(nas_index).Cells(n, 10).Value = re
             '------------------------------
            End If

         Next o
        Next n
    Else
        'MsgBox exit loop
        Exit For
    End If

Next d



'------------------nevermind below
Dim ntime As Date, nStrTime As String
If Not ThisWorkbook.ActiveSheet.Cells(1, 10).Value = "" Then
'-new time
ThisWorkbook.ActiveSheet.Cells(1, 12).Value = Time()
ntime = ThisWorkbook.ActiveSheet.Cells(1, 12).Value
mstrtime = Format(ntime, "hh:mm:ss:ms")
ThisWorkbook.ActiveSheet.Cells(1, 12).Value = mstrtime

'-old time
gettime = ThisWorkbook.ActiveSheet.Cells(1, 10).Value
ThisWorkbook.ActiveSheet.Cells(1, 11).Value = gettime
myStrTime = Format(gettime, "hh:mm:ss:ms")
ThisWorkbook.ActiveSheet.Cells(1, 11).Value = myStrTime
End If

End Sub

The image below is so far what I got (the text in red, is what i wish to have). enter image description here

My goal is to have the following Check if I bought the same item before (ID). Collect data of this ID and store it in the column History. So that I can see if the product has been price changed over the previous weeks. I can't get the data properly of previous sheets. Instead of getting the following:
item: A B C D or
item: D C B A
I get something like this:
item: A A A A A A B B B B B B C C C C C C D D D D D D or
item: A B C D A B C D A B C D

I think I am failing here:

If prid_new = prid_old Then
                 'Below is not working properly
                 '------------------------------
                    re = re & " " & pre_amw 
                    Sheets(nas_index).Cells(n, 10).Value = re
                 '------------------------------
                End If

Can someone lent me a hand.

Aucun commentaire:

Enregistrer un commentaire