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).
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