Hello, for the coding in bold, i am wondering if the system returns the cell or the values in the cell to the user? Can someone guide me as the vlookup function cannot find any values in another worksheet. Thanks and appreciate all helps


[VBA]
Private Sub findTxn()

Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

Dim myRange As Range, oneArea As Range, matchPage As Variant, csRange As Range, csArea As Range, Y As Variant

'activate this workbook
Workbooks("bbca_mgt_report_wip(new).xls").Activate

'activate this worksheet
Sheets("Vol").Activate

'use loop to find and match different txn
With Sheets("vol").Range("C:C") '4
On Error Resume Next
Set myRange = .SpecialCells(xlCellTypeConstants)
Set myRange = .SpecialCells(xlCellTypeFormulas)
Set myRange = Application.Union(myRange, .SpecialCells(xlCellTypeConstants))
End With

If myRange Is Nothing Then MsgBox "Nothing in col C": Exit Sub
Workbooks("bbca volume report 2010_may_team.xls").Activate
Sheets("CS-EB 2010").Activate

For Each oneArea In myRange.Areas

With oneArea

Set lookFor = oneArea
Set rng = Workbooks("bbca volume report 2010_may_team.xls").Sheets("CS-EB 2010").Columns("A:A")
col = 2
On Error Resume Next
found = Application.VLookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox "The look-up value of " & lookFor & " is " & found & " in column " & col
End If
On Error GoTo 0


End With
Next oneArea

End Sub
[/VBA]