PDA

View Full Version : Error when rng.find is nothing



CodeNinja
08-16-2012, 07:04 AM
I have an odd circumstance. I run a program I wrote and it works fine on my computer while on my co-workers computer it breaks with err 91 object variable or with block variable not set.

The code breaks on this line:
sBrand(1) = rng.Find(what:=Mid(sProperties(1), 1, 5), after:=rng.Cells(rng.Cells.Count)).Offset(0, 2)

What is happening on my computer is that sbrand(1) is nothing because the rng.find found no match. This is expected.

My co-worker is running windows 7 and IE 9 while I am running xp professional and IE 8. That is the only real difference I can think of.

The question is: Is there some reason I cannot do variable(1) = rng.find(blah) when rng.find(blah) returns nothing? do I need to do set variable(1) =... or something?


Thanks,

CodeNinja.

Bob Phillips
08-16-2012, 07:08 AM
Shouldn't it be

Set sBrand(1) = ...

CodeNinja
08-16-2012, 07:25 AM
XLD,
Exactly what I thought (I even put that in first message) but when I add "Set " to the beginning of that line, the co-worker gets "Object Required" error...

Kenneth Hobs
08-16-2012, 07:29 AM
Did you Dim the array as Range?

Were you wanting to set that element to the Range or the Value?

CodeNinja
08-16-2012, 07:47 AM
I am calling a function... see below. I did dim the array and then redim it to give it parameters. I am trying to set the value of sBrand(1)

Function fillPropertiesAndBrands(sProperties() As String) As String()
'This function returns a multidimensional array of containing brand and property
'In this function, we set the userform ufPropertySelector brand listbox with data
'We also fill the invisible label lbBrandProperties with an array to be retrieved
'to handle moving data as the user selects and deselects brands.

Dim i As Integer
Dim iBrandCounter As Integer
Dim iPropertyCounter As Integer
Dim rng As Range
Dim sProp() As String
Dim sBrand() As String
Dim sThisBrand As String
Dim iBrandExists As Integer
Dim iTemp As Integer
Dim bEnteredProp As Boolean
Dim rngBrand As Range
Dim sTest As String


iBrandCounter = 1
iPropertyCounter = 1

'clear ufPropertySelector
With ufPropertySelector
.lbDontUseBrand.Clear
.lbDontUseProp.Clear
.lbUseBrand.Clear
.lbUseProp.Clear
End With

Set rng = Sheets("Properties").Range("A2:E" & Sheets("Properties").Range("A65536").End(xlUp).Row)
'**********************************************************************
' evidently you cannot redim preserve anything but the last dimension *
' of an array, so i need to establish the quantity of brands first *
'**********************************************************************
ReDim sBrand(1 To 1)

For i = 1 To UBound(sProperties)
Set rngBrand = rng.Find(what:=Mid(sProperties(i), 1, 5), after:=rng.Cells(rng.Cells.Count))
If Not rngBrand Is Nothing Then
sThisBrand = rngBrand.Offset(0, 2)
iBrandExists = 0
If sBrand(1) = "" Then sBrand(1) = sThisBrand
For iTemp = 1 To UBound(sBrand)
If sThisBrand = sBrand(iTemp) Then iBrandExists = iTemp
Next iTemp
If iBrandExists = 0 Then
ReDim Preserve sBrand(1 To 1 + UBound(sBrand))
sBrand(UBound(sBrand)) = sThisBrand
End If
End If
Next i
'now that we have counted the brands, clear sbrand
For i = 1 To UBound(sBrand)
sBrand(i) = ""
Next i

'Fill the arrays sprop and sbrand
ReDim sProp(1 To UBound(sBrand), 1 To 1)
sBrand(1) = rng.Find(what:=Mid(sProperties(1), 1, 5), after:=rng.Cells(rng.Cells.Count)).Offset(0, 2)
sProp(1, 1) = sProperties(1)
For i = 2 To UBound(sProperties)

iBrandExists = 0

Set rngBrand = rng.Find(what:=Mid(sProperties(i), 1, 5), after:=rng.Cells(rng.Cells.Count))
If Not rngBrand Is Nothing Then
sThisBrand = rngBrand.Offset(0, 2)
For iTemp = 1 To UBound(sBrand)
If sBrand(iTemp) = sThisBrand Then iBrandExists = iTemp
Next iTemp
If iBrandExists = 0 Then 'new brand
iBrandCounter = iBrandCounter + 1
'add to sbrand() and sprop()
sBrand(iBrandCounter) = sThisBrand
sProp(iBrandCounter, 1) = sProperties(i)
Else 'brand exists, put in that brand array group
bEnteredProp = False
For iTemp = 1 To UBound(sProp, 2)
If sProp(iBrandExists, iTemp) = "" Then
If bEnteredProp = False Then
sProp(iBrandExists, iTemp) = sProperties(i)
bEnteredProp = True
End If
End If
Next iTemp
If bEnteredProp = False Then
iPropertyCounter = iPropertyCounter + 1
ReDim Preserve sProp(1 To UBound(sBrand), 1 To iPropertyCounter)
sProp(iBrandExists, iPropertyCounter) = sProperties(i)
End If
End If
End If
Next i

'store brands in useform
For i = 1 To UBound(sBrand)
ufPropertySelector.lbDontUseBrand.AddItem sBrand(i)
Next i

'store this array in lbbrandproperties to be retrieved for running macros on ufPropertySelector
ufPropertySelector.lbBrandProperties.Clear
For iTemp = 1 To UBound(sBrand)
For i = 1 To UBound(sProp, 2)
If Len(sProp(iTemp, i)) > 0 Then
ufPropertySelector.lbBrandProperties.AddItem (sBrand(iTemp) & ":-:-:" & sProp(iTemp, i))
End If
Next i
Next iTemp

fillPropertiesAndBrands = sProp

End Function

Kenneth Hobs
08-16-2012, 08:15 AM
In that case:
Dim r As Range
Set r = Rng.Find(what:=Mid(sProperties(1), 1, 5), _
after:=Rng.Cells(Rng.Cells.Count)).Offset(0, 2)
If r Is Nothing Then
sBrand(1) = ""
Else
sBrand(1) = CStr(r.Value)
End If

snb
08-16-2012, 08:19 AM
I can't figure out exactly what you are trying to accomplish, but you can assign a multidimensional range to a combobox or listbox using:


Listbox1.list=cells(1).currentregion.value
Combobox1.list= range("F5:P300").value


Using application.index you can assign an array filtered by row and/or column.

@KH

I'd prefer format(r.value) to cstr(r.value) because it doesn't add anything.

CodeNinja
08-16-2012, 12:07 PM
First, thanks for the responses guys.

I am getting a second laptop with the settings of my co-worker, so I will test both these suggestions when that is provided. Will update soon... :friends:

Kenneth Hobs
08-16-2012, 02:13 PM
Another option is to use r.Text to make the format as the string. Getting the value or the string depends on ones need.