Xcel obj model (1 Viewer)

[mission2java_78]

Im using the find method of the Excel object model...I need to find the first cell in a row where there is no value...
I tried the following:

Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("C:\jhwork\Book1.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1")
objXL.Visible = True

rs.MoveLast
rs.MoveFirst

objXLWrkSht.Range("A1").Activate

'MsgBox objXL.ActiveCell

'Set myRange = objXLWrkSht.Range("A1")



objXLWrkSht.Cells.Find("", , , , xlByRows, xlNext).Activate


'myRange.Activate
'MsgBox myRange.Row
'MsgBox myRange.Column

MsgBox objXL.ActiveCell
MsgBox objXL.ActiveCell.Row
MsgBox objXL.ActiveCell.Column

Pay close attention to the line:

objXLWrkSht.Cells.Find("", , , , xlByRows, xlNext).Activate

I specified xlByRows...and it searches the columns..instead. Then me thinking ok maybe these are switched in excel so Ill try the constant xlByColumns. So I changed it to:

objXLWrkSht.Cells.Find("", , , , xlByColumns, xlNext).Activate

Only to get an error message saying with block or object not set? What the hell first time it doesnt search by rows..but by columns even though I ask it by row. Then I specify columns and I get an error?

Anyone shed some light on MS's object model here...

Jon

 

[sambo]

First of all, you are using "A1" as your range. This means that you are only searching Cell A1.
This method works nicely to search cell by cell.

Dim d as Variant      
       With objXLWrkSht.Range("a1:a99") 'your range here 
        Set d = .Find("", LookIn:=xlValues)
        If Not d Is Nothing Then
            MsgBox "Nothing in this cell" & vbCrLf & d.Address 'Print cell reference            
        Else
           'MsgBox "There is something in this cell"
        End If
      
      End With

I may have the Double Negative If/Else in the wrong order, but you get the general idea.

Pass in a Range to make it more usable if you'd like.

Good Luck..