Hi,
I have made this small macro which should lookup 'sku' in worksheet 'Thistv' and write the 'number' found at the 10th column of the same row to the cell at worksheet 'Tvad' in a matrix at the cell corresponding with 'sku' and 'day'. 'day' can be found in the 8th column of the worksheet 'Thistv'.
Sub NumberSkuDay()
'clear matrix
Tvad.Activate
Tvad.Range(Cells(2, 2), Cells(261, aant_sku + 1)).ClearContents
FinalCol = 10
Dim g As Range
For k = 2 To FinalCol
sku = Tvad.Cells(1, k).Value
Set g = Thistv.Columns(1).Find(sku, LookIn:=xlValues, Lookat:=xlWhole)
If Not g Is Nothing Then
AdresFirst= g.Address
Do
' search information at Worksheet 'Thistv'
day= Thistv.Cells(g.Row, 8).Value
number= Thistv.Cells(g.Row, 10).Value
' edit number in worksheet 'Tvad'
dayrow= Tvad.Columns(1).Find(day, LookIn:=xlValues, Lookat:=xlWhole).Row
Tvad.Cells(dayrow, k).Value = Tvad.Cells(dayrow, k).Value + number
' go to the next match with sku in the worksheet 'Thistv'
Set g = Thistv.Columns(1).FindNext(after:=g)
Loop While Not g Is Nothing And g.Address <> AdresFirst
End If
Next k
End Sub
I get problems in the line in bold, because it does not find a next value, although there are still a lot of matches,
I think the problem is situated in the lines before, where I go first to an other worksheet, and then the method .findnext doesn't work anymore,
any suggestions? Thanks a lot!
Bookmarks