PDA

View Full Version : Find all function returns the cell or the value?



TheAnswer
12-05-2010, 08:44 PM
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:)



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

mikerickson
12-05-2010, 10:57 PM
Try changing this line
For Each oneArea In myRange.Cells

TheAnswer
12-05-2010, 11:10 PM
Hi, thanks..But it didnt work:(

Bob Phillips
12-06-2010, 02:52 AM
I think Mike might have meant



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

mikerickson
12-06-2010, 07:30 AM
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
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 Also, turning error checking back on might help isolate problems.

austenr
12-06-2010, 08:09 AM
What Mike means is put

0:

right before

End Sub

mikerickson
12-06-2010, 12:27 PM
No I don't mean that. On Error Goto 0 (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
12-06-2010, 12:47 PM
My sincerest apologies :(

TheAnswer
12-06-2010, 08:35 PM
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?


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

austenr
12-06-2010, 08:59 PM
Looks like oneArea is a cell within a group of cells.

Bob Phillips
12-07-2010, 12:59 AM
I think oneArea is a range of cells, hence my suggestion of iterating through each cell in that range.

TheAnswer
12-10-2010, 03:17 AM
Thanks all for the help:D
I had solved my problem:D
Ya, indeed it is a cell not a value.