Find an item in an excel list from within visio

[matthew]

Hi,
My code calls an excel file and then interrogates the data.  There is lots of it so looping through each item in the lists is very time consuming.
So I thought of using some code that I know works in Excel but doesn't appear to work in visio:
rownumber = objXLApp.ActiveWorkbook.Sheets(thissheet).Cells.Find(thisstring, Range("E1"), xlValues).Row
I get the error that the Sub or Function is not defined with 'Range' highlighted.  Changing xlRng (see below) helped though the error moved on to the 'xlValues'.  If I change this to -4163 then I get a type mismatch error (rownumber is declared as Long).

When I try to declare a range such as doing it this way:
Set xlWksht = objXLBook.Worksheets(thissheet) '//...worksheet by name
Set xlRng = xlWksht.UsedRange.Find(thisitem)
rownumber = xlRng.Row
I get run time error 91 – Object variable or With Block variable not set with the 'rownumber = xlRng.Row' highlighted

I should add that the ranges aren't named in excel (and won't be). Sometimes I need to find an item in column E, sometimes it is column A and so on.
Does anyone know a quick way of finding an item in an excel list when using visio?

Thanks for your help,
Matthew

[Croc]

You can take the previous example from Visio Guy as a basis and replace several lines in it as shown below.

Code Select Expand

Option Explicit

Public Sub TalkToExcel()

  Const WorksheetName As String = "Customers" '//...AKA: mssheet in your case

  '// Get the Excel application:
  Dim xlApp As Object
  Set xlApp = GetObject(, "Excel.Application")
  Debug.Print xlApp, TypeName(xlApp)
 
  '// Get the active workbook:
  Dim xlWkbk As Object
  Set xlWkbk = xlApp.ActiveWorkbook
  Debug.Print "Workbook: " & xlWkbk.Name
 
  '// Get a worksheet from the workbook:
  Dim xlWksht As Object
  'Set xlWksht = xlApp.ActiveSheet '...active worksheet
  Set xlWksht = xlWkbk.Worksheets(WorksheetName) '//...worksheet by name
  Debug.Print "Worksheet: " & xlWksht.Name
 
  '// Get the used range:
'  Dim xlRng As Object
'  Set xlRng = xlWksht.UsedRange
'  Debug.Print VBA.TypeName(xlRng)
'  Debug.Print "Rows: " & xlRng.Rows.Count
 
  Dim xlRng As Object
  Set xlRng = xlWksht.Range("D1:D1000") 'scope
  Dim thisstring As String
  Dim rownumber As Long
  thisstring = "erer"                   'sample
  On Error Resume Next
  Err.Clear
    rownumber = xlRng.Find(thisstring, xlRng.Cells(1), -4163).Row
  If Err.Number <> 0 Then
    MsgBox "Sample " & thisstring & " not found"
  Else
    MsgBox thisstring & " found on Row " & rownumber
  End If
  On Error GoTo 0
 
Cleanup:
  Set xlRng = Nothing
  Set xlWksht = Nothing
  Set xlWkbk = Nothing
  Set xlApp = Nothing
End Sub

[matthew]

Hi,
I tried the code but something weird is happening with the range.
The code isn't recognising that it needs to look in one column only (E in this case).
It is finding an item in another column (H). Thought '("E4:E" & start - 1)' might be the problem but even if I convert 'start-1' to a string it is the same result.
If I remove the item from Column H  I get the result I expect i.e. taskidfound = false.
Could 'xlRng.Cells(1)' be the issue?

                            Set xlRng = Nothing
                            Set xlWksht = Nothing
                            Set xlWksht = objXLBook.Worksheets(succsheet1)
                            Set xlRng = xlWksht.Range("E4:E" & start - 1)
                            On Error Resume Next
                            Err.Clear
                                rownumber = xlRng.Find(linktaskid(y), xlRng.Cells(1), -4163).Row
                            If Err.Number <> 0 Then
                                taskidfound = False
                            Else
                                taskidfound = True
                            End If
                            Set xlRng = Nothing
                            Set xlWksht = Nothing


thanks for your help,
Matthew

[Surrogate]

Could 'xlRng.Cells(1)' be the issue?

Cells must have two arguments (row and column numbers)!
Worksheet.Cells property (Excel)

My bad, i didn't know that this statement mean first cell in range. 

[Croc]

What is "start-1"?
What range do you want to set this way?

[matthew]

Hi,
the size of the list I look through grows with each iteration, this is just the variable I use to resize the range (list) I need to look through.
So at the start it will E4:E4 but after the next iteration it'll be E4:E5 and so on, the code shown is part of a do while loop.
thanks
Matthew

[Surrogate]

What is "start-1"?

I guess there is start is variable. we dont know values in other variables: succsheet1, linktaskid, y and so on...

matthew, I think you can get help for this issue in some Excel's forums.

[matthew]

ok, thanks to everyone who contributed, it was very helpful
kind regards
Matthew

[OldSchool1948]

I use two functions to get either a value or the row number from an Excel worksheet. 

This function gets a value:

Code Select Expand

Public Function xlvLookup( _
                    ByVal xlWrkSheet As Excel.Worksheet, _
                    ByVal lookupRange As String, _
                    ByVal lookupValue As String, _
                    ByVal lookupRowOffset As Long, _
                    ByVal lookupColOffset As Long) As String
                           
    Dim FirstFound As String
   
    Dim myRange As Excel.Range
    Set myRange = xlWrkSheet.Range(lookupRange)
   
    Dim LastCell As Excel.Range
    Set LastCell = myRange.Cells(myRange.Cells.Count)
   
    Dim FoundCell As Excel.Range
    Set FoundCell = myRange.Find(What:=lookupValue, LookIn:=xlValues)
       
    'Test to see if anything was found
    If Not FoundCell Is Nothing Then
        FirstFound = FoundCell.Address
    Else
        GoTo exitHere
    End If
   
    Dim rng As Excel.Range
    Set rng = FoundCell
   
    'Loop until cycled through all unique finds
    Do Until FoundCell Is Nothing
   
        'Find next cell with lookupValue value
        Set FoundCell = myRange.FindNext(After:=FoundCell)
         
        ''' Look for value in range
        If FoundCell.Value = lookupValue Then
       
            xlvLookup = FoundCell.Offset(lookupRowOffset, lookupColOffset).Value
           
            GoTo exitHere

        End If
       
        'Test to see if cycled through to first found cell
        If FoundCell.Address = FirstFound Then Exit Do
       
    Loop
                                       
exitHere:
   
End Function
I call it like this:

Code Select Expand

strOUName = xlvLookup( _
                        xlWs_lists, _
                        strLookupRange, _
                        strSubnetCode, _
                        0, _
                        1)
This function gets a row number:

Code Select Expand

Public Function xlvLookupRowID( _
                    ByVal xlWrkSheet As Excel.Worksheet, _
                    ByVal lookupRange As String, _
                    ByVal lookupValue As String, _
                    ByVal lookupRowOffset As Long, _
                    ByVal lookupColOffset As Long) As Long
                           
    Dim FirstFound As String
   
    Dim myRange As Range
    Set myRange = xlWrkSheet.Range(lookupRange)
   
    Dim LastCell As Range
    Set LastCell = myRange.Cells(myRange.Cells.Count)
   
    Dim FoundCell As Range
    Set FoundCell = myRange.Find(What:=lookupValue, LookIn:=xlValues)
       
    'Test to see if anything was found
    If Not FoundCell Is Nothing Then
        FirstFound = FoundCell.Address
        xlvLookupRowID = FoundCell.Row
    End If
                                               
End Function
I call it like this:

Code Select Expand

foundRow = xlvLookupRowID( _
                      xlWs_PPTable, _
                      strLookupRange, _
                      strConId, _
                      0, _
                      0)