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
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
Code:
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
Last edited: