Code Stopped working

[robo0502]

Hi guys,

wondering if you can help with this one. I have a code which should find an empty cell in a column on one sheet, take value from another columnt in same row and find it on another sheet to check status of this value in another column. when I put this code in place it worked perfectly but then from time to time it stopped working. Already couple of times. What I always did was to run this code using F8 and then somehow it stars working again in standard mode as well. but the issue still reoccurs. Could you pls have a look at the code let me know how to adjust it to prevent this?

Th problem is that the code cant find the value on the second sheet even though the value is there. I highlighted the problematic section below as bold.

thank you

Rob

Option Compare Text


Sub RMA_update()
Dim index As String
Dim FindEmpty As String
Dim Rn As Range
Dim row As Double
On Error GoTo Errhandler
Application.ScreenUpdating = False


'check if it is collectedlastrow = Sheets("Returns - Refund").Range("A1048576").End(xlUp).row
FindEmpty = Empty


row = 2
Loop6:
    With Sheets("Returns - Refund").Range("N" & row & ":N" & lastrow)
        Set Rn = .Find(What:=FindEmpty, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        If Not Rn Is Nothing And row <> lastrow + 1 Then
            'Application.Goto Rn, True
            row = Rn.row
            If Sheets("Returns - Refund").Cells(row, 8) <> "" Then
                index = Sheets("Returns - Refund").Cells(row, 8)             
    
    If Trim(index) <> "" Then
        Sheets("Flex report").Select
        With Sheets("Flex report").Range("U:U")
            Set Rng = .Find(What:=index, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        
[B]            If Not Rng Is Nothing Then[/B]
                If (Sheets("Flex report").Cells(Rng.row, 41).Text <> "" And Sheets("Flex report").Cells(Rng.row, 41) <> 0 / 1 / 1900) Or Sheets("Flex report").Cells(Rng.row, 11).Text = "10" Or Sheets("Flex report").Cells(Rng.row, 11).Text = "11" Then
                    Sheets("Returns - Refund").Cells(row, 14) = Date
                End If
            End If
        End With
    End If
            
            End If
            
            'if CN is sooner than collection date
            If Left(Sheets("Returns - Refund").Cells(row, 10), 1) = 6 Then
                Sheets("Returns - Refund").Cells(row, 14) = Date
            End If
                       
            row = row + 1
            GoTo Loop6
        End If
    End With

MsgBox "Data updated"

Application.ScreenUpdating = True


Exit Sub
Errhandler:
        MsgBox "An error has occurred. Number: " & Err.Number & ", description: " & Err.Description


End Sub

 

[Yongle]

The value may not be an exact match or perhaps VBA is looking for the wrong value etc

THERE IS AN OBVIOUS TYPO IN THIS SENTENCE BUT Y0U MAY HAVE D1FFICULTY SPOTTING IT ON THE FIRST READING
- what is the error?

Resetting one of the variables may be the reason the code works correctly. Or perhaps something else is changing.

Something you could try is to create a sheet named "Log" and monitor the key values to see if something looks wrong those times when VBA fails to find the value you "know" is there

Insert something like this ...

    Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3).Value = _
        Array([COLOR=#ff0000]Now[/COLOR], [COLOR=#006400]Index[/COLOR], [COLOR=#ff0000]WorksheetFunction.CountIf(Sheets("Flex report").Range("U:U"), Index)[/COLOR])

above this line

   If Trim(Index) <> "" Then

Now adds a timestamp
Index tells you which value VBA is seeking
CountIf is a different way to prove the value's existence (0 means it does not exist in the range)

 

[robo0502]

Hi Yongle,

it gives me 0 even if value is there. I can easily find it using CTRL+F. Any idea why?

thank you

Rob

 

[Yongle]

I think you are saying that CountIf is giving a count of ZERO which means that there is no cell in column U (in sheet Flex Report) with a value matching value of INDEX
So you need to look very carefully at both values - there appears to be an inconsistency between them

Q1 are you trying to match the value of INDEX with the whole cell value or part of it

assuming INDEX = dog, which of these is the correct match [U3 only? both cells?]
U2 = dog at home
U3 = dog

Q2 please provide a typical value for Index

Q3 how are the values in column U created?
- if by formula, post the formula


thanks

 

[robo0502]

Hi Yongle,

pls see answers to your questions:
Q1 - whole cell value
Q2 - 4221796624
Q3 - The values are copied and pasted there manually from another report before macro is run.

thank you for your help