Excel: Object variable or With block variable not set: Problem 1

[Shippwreck]

Hi Everyone,

I've been coding in VBA for a little bit now, but still consider myself a beginner, basically i get most things done by recording a bit and then adapting that code to what i need. This works well and i can do a lot of things this way except i don't really understand the errors that pop up and still don't really understand all of the explanation to them either.

So this causes a problem with the above error as my method breaks down. Here it is, I need to search a selection to find a certain piece of text, this search needs to be repeated about 50 times or so with different text. To achieve this i created an array to contain the values and then loop through the search the number of times in the array. Below is the code, should make it clearer:

Do
If ArrayName1(Number1) = "END" Then
Exit Do
Else
Set c = Cells.Find(What:=ArrayName1(Number1), LookAt:=xlPart)
If c Is Nothing Then
Number1 = Number1 + 1
Else
Range("A9:A1000").Select
********Selection.Find(What:=ArrayName1(Number1), LookAt:=xlPart).Activate********
Range("A9:A1000").Select
Row1 = ActiveWindow.Selection.Row
CellSelection1 = ("D" & Row1)
Range(CellSelection1).Copy
Windows("Working File V3.xls").Activate
Sheets("Base Data Q1 04 - 05").Select
Cells.Find(What:=ArrayName1(Number1), _ LookAt:=xlWhole).Activate
Row2 = ActiveWindow.Selection.Row
CellSelection2 = ("E" & Row2)
Range(CellSelection2).Select
ActiveSheet.Paste
Number1 = Number1 + 1
Windows("data.xls").Activate
Sheets("code").Select
End If
End If
Loop

It is the bit with stars around that VB highlights in yellow when i debug. Just to make it a little bit more complicated i have 9 different arrays so in a form that appears before this loop i assign which array i want to use to ArrayName1.

Just for som background if i take out Range("A9:A1000").Select and change Selection to Cells it works fine (but searches the whole sheet which i can't have) When i try my way of recording a search it gives:

Range("A9:A1000").Select
Selection.Find(What:="TAN", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

And if i use that (except change the search to the array) it doesn't work. Therefore i have come to the conclusion that it is how i declare the array, which looks like this:

Public ArrayName1
Public STTCode 'Name of one of my arrays

Any help would be greatly appreciated, thanks a lot

http://www.orlandoluxuryvilla-pool.com

 

[Loomah]

Hi
Without recreating your entire scenario I think the most likely problem is that the value you are looking for doesn't exist in the range you are searching.

That said your code should trap that problem! What would happen if you substitute your problem line with
c.activate
having declared c as range?

Below is the code I've been using to try to recreate your problem, applying it to a range of numbers between 1 & 100. It may help working with a smaller bit of code to identify where problems may arise. Take out the error handling to generate errors.

Sub q()
Dim vArr As Variant
Dim i As Integer
On Error GoTo Loom

vArr = Array(11, 33, 44, 121, 68)
For i = LBound(vArr) To UBound(vArr)
    MsgBox vArr(i) & " is in cell " & _
        Range("A1:E15").Find(What:=vArr(i), LookAt:=xlWhole).Address
Next

Exit Sub
Loom:
    MsgBox vArr(i) & " cannot be found."
    Resume Next

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Shippwreck]

Well done Loomah,

You were right first time, it was that the value didn't exist. Or more precisely it did just not how i wanted it! I'll explain with the example causing the problem.

In the first sheet "code" it doesn't, this i knew, but it is a value that could exist in the future therefore i needed to search for it still. What i hadn't realised was that it did exist but as "learning diSABilities" in another cell. Now this caused a problem because at the start i check to see if the value exists but i search the whole sheet not just the selection so it finds it but when i activate that search it then can't find it because i'm searching only the selection and it doesn't exist in the selection.

Just as a point in that sheet it is actually likely to exist with a 4th undetermined character which is why i have to use xlpart and not xlwhole. in the second sheet i KNOw it will only be three characters so i can use xlwhole to avoid the above problem!

When i changed the first check to only search the selection it works perfectly,

Thank you, i'd have probably gone around forever with that one!

http://www.orlandoluxuryvilla-pool.com

 

[xlbo]

Just for reference, I generally use this kind of construct to debug the find function:

dim fCell as range

Set fCell = Range("A9:A1000").Find(What:=ArrayName1(Number1), LookAt:=xlPart)

[b]If Not fCell is Nothing then[/b]
  'Value has been found
   Else
  'Value has not been found
   End if

Rgds, Geoff

[COLOR=blue][i]"Having been erased. the document thjat you are seeking. Must now be retyped"[/i][/color]

[COLOR=green]Please read FAQ222-2244 before you ask a question [/color]