Search value

[ismailr]

Hi,
I have to sets of values. One is supplier Invoices and second One is supplier Invoices along with the supplier code. These are two set of values generated from two different systems. Please see the attached sheet.
I wanted to reconcile the supplier invoices. If the invoice from first set is available in second set then it is treated as "Accounted" otherwise "Not Accounted".

Please help me to write VBA to get the tag as "Acconted" Or "Not Accounted" in front of first set of value.
Thank in anticipation.

[fixo]

This might help with your problem I hope


Public Sub CheckOnAccounts()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim srng As Range
Dim i, j, k As Long
Dim searchStr, findStr, trimStr As String
On Error GoTo WhatA
Set rng1 = ActiveSheet.Range("A2:A6")
Set rng2 = ActiveSheet.Range("E2:E9")
For i = 1 To rng1.Count
searchStr = CStr(rng1.Cells(i, 1).Value)
If searchStr <> "" Then
For j = 1 To rng2.Count
findStr = CStr(rng2.Cells(j, 1).Value)
trimStr = Right(findStr, Len(searchStr))
If trimStr = searchStr Then
rng1.Cells(i, 1).Offset(0, 1).Value = "Accd"
Exit For
End If
Next
End If
Next
WhatA:
MsgBox Err.Description
End Sub


Fatty

~'J'~

[ismailr]

It work fine. thanks

[fixo]

It work fine. thanks
Happy computing
Cheers :)