Get column values after doing a Find in Excel using VBScript

Question

I use the following code to find a specific text in the whole Excel sheet and was able to get to that specific cell. With that information, I wanted to read each of the column values for that active row.

Dim ObjXL
Const xlWhole = 1
Set ObjXL = CreateObject("Excel.Application")

ObjXL.Workbooks.Open ("C:\Config.xls")
Set objWorksheet = ObjXL.Worksheets("Sheet1")
Set objRange = objWorksheet.UsedRange
Set objTarget = objRange.Find("Honda", , , xlWhole) ' The searched value is on column 3.

I tried using the Offset method and it seems to work:

Msgbox objTarget.Offset(, -2).Value

The Excel sheet has 7 columns and passing -2 to the Offset method gets me the value in the first column. How do I get the first and last column indexes dynamically for that row to pass them to the Offset method. Or better, I would like to be able to get the column values based on the column names.

Thanks.

Answer 1

Not entirely clear what you want, but the first cell in the row is probably:

Cells(objTarget.Row, objRange.Column)

and the last cell

Cells(objTarget.Row, objRange.Columns(objRange.Columns.Count).Column)

A range object representing the entire row would be:

objRange.Rows(objTarget.Row)