Results 1 to 3 of 3

vba .findnext gives 'nothing'

Threaded View

Maaike... vba .findnext gives 'nothing' 04-17-2012, 10:30 AM
Andrew-R Re: vba .findnext gives... 04-17-2012, 10:41 AM
Maaike... Re: vba .findnext gives... 04-17-2012, 10:57 AM
  1. #1
    Registered User
    Join Date
    04-17-2012
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    4

    vba .findnext gives 'nothing'

    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!
    Last edited by Maaike...; 04-17-2012 at 10:39 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1