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
You can take the previous example from Visio Guy as a basis and replace several lines in it as shown below.
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
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
Quote from: matthew on October 06, 2020, 10:07:54 AM
Could 'xlRng.Cells(1)' be the issue?
Cells must have two arguments (row and column numbers)!Worksheet.Cells property (Excel) (https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.cells)
My bad, i didn't know that this statement mean first cell in range. :o
What is "start-1"?
What range do you want to set this way?
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
Quote from: Croc on October 06, 2020, 11:07:58 AM
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.
ok, thanks to everyone who contributed, it was very helpful
kind regards
Matthew
I use two functions to get either a value or the row number from an Excel worksheet.
This function gets a value:
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:
strOUName = xlvLookup( _
xlWs_lists, _
strLookupRange, _
strSubnetCode, _
0, _
1)
This function gets a row number:
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:
foundRow = xlvLookupRowID( _
xlWs_PPTable, _
strLookupRange, _
strConId, _
0, _
0)