Iterating thru cells in Excel 2010

Dev Shed Forums Sponsor:

February 27th, 2012, 04:03 PM

Ranakhu

Contributing User

Join Date: Jan 2009

Posts: 65

Time spent in forums: 13 h 36 m 42 sec
Reputation Power: 14

Iterating thru cells in Excel 2010

Hello,

I am not sure why I am getting this error with line If statement in the code below...any ideas are appreciated. Get UNKNOWN RUNTIME ERROR

Code:

' do excel stuff	
Set xl = CreateObject("Excel.Application")
	
' no need to launch excel
xl.visible = False
	
' specify excel file path and name
sExcelPath = CurProjDir & "\comp_pins_nets.xlsx"	
	
'Set wb = xl.Workbooks.Open(sExcelPath)	
	
Set wb = xl.Workbooks.Open(sExcelPath, ,ReadOnly)
		
Set oCurWorkSheet = wb.Worksheets("Sheet1")
			
'Find used rows & cols
usedColsCount = oCurWorkSheet.UsedRange.Columns.Count
usedRowsCount = oCurWorkSheet.UsedRange.Rows.Count	

'Find column & row that has "Pin Name"		
For row = 0 To usedRowsCount	
   For column = 0 To usedColsCount		
     If oCurWorkSheet.Cells(row, column).Value = "Pin Name" Then
	iRow = row
	iCol = column				
    End If			
   Next			
Next

February 27th, 2012, 04:33 PM

medialint

Type Cast Exception

Dev Shed God 20th Plane (14500 - 14999 posts)

Join Date: Apr 2004

Location: OAKLAND CA | Adam's Point (Fairyland)

Posts: 14,938

Folding Points: 319635 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 6 Months 2 Weeks 2 Days 19 m 8 sec
Reputation Power: 8490

Rows and Columns start at 1 not 0.

__________________
medialint.com

“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

February 27th, 2012, 04:39 PM

Ranakhu

Contributing User

Join Date: Jan 2009

Posts: 65

Time spent in forums: 13 h 36 m 42 sec
Reputation Power: 14

Quote:

Originally Posted by medialint

Rows and Columns start at 1 not 0.

Thank you very much

February 27th, 2012, 04:41 PM

medialint

Type Cast Exception

Join Date: Apr 2004

Location: OAKLAND CA | Adam's Point (Fairyland)

Posts: 14,938

Time spent in forums: 6 Months 2 Weeks 2 Days 19 m 8 sec
Reputation Power: 8490

You could just do something like this instead of looping through the whole worksheet:

Code:

    With xl.ActiveSheet
        nRow = .Cells.Find("pin name").Row
        nCol = .Cells.Find("pin name").Column
    End With

February 27th, 2012, 06:19 PM

Ranakhu

Contributing User

Join Date: Jan 2009

Posts: 65

Time spent in forums: 13 h 36 m 42 sec
Reputation Power: 14

Quote:

Originally Posted by medialint

You could just do something like this instead of looping through the whole worksheet:

Code:

    With xl.ActiveSheet
        nRow = .Cells.Find("pin name").Row
        nCol = .Cells.Find("pin name").Column
    End With

Thank you again

Iterating thru cells in Excel 2010

Developer Shed Advertisers and Affiliates