hi,
i have the following code.
it gives the following error :
"Run-time error '91' :
Object variable or With Block variable not set.
i notice i have this error when the variable R.Address gets the value nothing and get's compared with FindAddress variable ( which is a cell reference).
is there a way to override this error as it ends my code
thank you in advance
i have the following code.
it gives the following error :
"Run-time error '91' :
Object variable or With Block variable not set.
i notice i have this error when the variable R.Address gets the value nothing and get's compared with FindAddress variable ( which is a cell reference).
is there a way to override this error as it ends my code
Code:
Sub A_findall()
'extract needed line from the invoice
Sheets("CALCULATION").Activate
Dim R As Range, FindAddress As String
toFind = "707884961114"
LR = Range("A" & Rows.Count).End(xlUp).Row
'Search for all the occurrences of the item.
With Range("A1:A" & LR)
Set R = .Find(toFind, LookAt:=xlPart)
'If a match is found.
If Not R Is Nothing Then
'Store the address of the cell where the first match is found in a variable.
FindAddress = R.Address
'Start to loop.
Do
R.Offset(0, 2).Value = Mid(R.Offset(0, 0), 18, 8) ' value from middle
R.Offset(0, 3).Value = Right(R.Offset(0, 0), 5) 'value from right
R.Offset(0, 1).Value = "'" & Left(R.Offset(0, 0), 16) 'value from right
R.Offset(0, 4).Value = R.Offset(2, 0)
R.Offset(0, 5).Value = R.Offset(4, 0) + R.Offset(5, 0) - R.Offset(11, 0)
R.Offset(0, 6).Value = R.Offset(6, 0)
R.Offset(0, 7).Value = R.Offset(8, 0)
R.Offset(0, 8).Value = R.Offset(10, 0)
R.Offset(0, 9).Value = R.Offset(12, 0) + R.Offset(14, 0)
R.Offset(0, 10).Value = R.Offset(14, 0)
R.Offset(0, 11).Value = R.Offset(16, 0)
R.Offset(0, 12).Value = "OK"
R.Offset(0, 0).Value = "C-" & Right(R.Offset(0, 1), 4)
R.Offset(0, 13).Value = "OK"
'Search the next cell with a matching value.
Set R = .FindNext(R)
'Loop as long matches are found, and the address of the cell where a match is found,
'is <> as the address of the cell where the first match is found (FindAddress).
Loop While Not R Is Nothing Or R.Address <> FindAddress
End If
End With
'Clear memory.
Set R = Nothing
End Sub
thank you in advance