Find all function returns the cell or the value?

[TheAnswer]

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]

[mikerickson]

Try changing this line
[VBA]For Each oneArea In myRange.Cells[/VBA]

[TheAnswer]

Hi, thanks..But it didnt work

[Bob Phillips]

I think Mike might have meant

[vba]

For Each oneArea In myRange.Areas

For Each cell In oneArea.Cells

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(cell.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
Next cell
Next oneArea
[/vba]

[mikerickson]

XLD's would achieve the result I was looking for, but I don't see the need for two loops.

I'm not clear as the desired result, but I notice that this isn't fully qualified[vba]
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))
On Error Goto 0
End With [/vba] Also, turning error checking back on might help isolate problems.

[austenr]

What Mike means is put

[VBA]0:[/VBA]

right before

[VBA]End Sub[/VBA]

[mikerickson]

No I don't mean that. [vba]On Error Goto 0[/vba] (zero not the letter o)
returns Error handling to its normal state. While it might be useful (nessary) in finished code, setting Error handling to Resume Next can mask errors during debugging.

[austenr]

My sincerest apologies

[TheAnswer]

Thanks all for the help and really appreciate it. I dont know if the codings are working or not as the msgbox didnt come out. And can someone tell me if oneArea is a cell or a value? If it is a value, can i make use of this statement?

[VBA]
x = application.match(oneArea, worksheets("CS-EB 2010").Range("A"),0)
[/VBA]

[austenr]

Looks like oneArea is a cell within a group of cells.

[Bob Phillips]

I think oneArea is a range of cells, hence my suggestion of iterating through each cell in that range.

[TheAnswer]

Thanks all for the help
I had solved my problem
Ya, indeed it is a cell not a value.