Return of cell loaction - row and column

Posted by Paul D on October 26, 2001 11:58 AM

Can anyone help me with some VB code; I'm opening a workbook and trying to find a specific cell based upon a variable. I need to know the cell (range value) so that I can then insert a row and shift that row to the left. I know how to insert and shift the columns, but am having a tough time with the find statement (trying to return a range)

Posted by Bob Umlas on October 26, 2001 1:06 PM

Set x=cells.find("whatever")
rw=x.row
col=x.column
adr=x.address

If it's not there, this'll produce an error, so trap for it:
On error Resume next
Err.Clear
Set x=cells.find("Whatever")
if err.number>0 then
' not found -- do something
endif

Posted by Paul D on October 26, 2001 5:15 PM

Still having a problem with this. Here is the code that I'm using.

Private Sub Merge_Click()
Dim xlapp As excel.Application
Dim wkbNewBook As excel.Workbook
Dim wksSheet As excel.Worksheet
Dim strBookName As String
Dim currentCell As Range

MsgBox ("here")
Set xlapp = New excel.Application
Set wkbNewBook = xlapp.Workbooks.Open(excelFile)

strBookName = "c:\test.xls"

Set wksSheet = wkbNewBook.Worksheets(1)
MsgBox ("set worksheet")

With wksSheet
Set currentCell = wksSheet.Cells.Find("518910")

MsgBox (currentCell.Row & " " & currentCell.Column & "--" & currentCell.Address)

wksSheet.Range(currentCell.Column).Columns.EntireColumn.Insert (xlShiftToRight)
wksSheet.Range(currentCell).Value = "Hello"
MsgBox ("insert column")

wkbNewBook.Close SaveChanges:=True, FileName:=strBookName
wksSheet = Nothing
wkbNewBook = Nothing
xlapp.Quit

MsgBox ("end")
End With

End Sub

It is giving an error indicating that the cells.find syntax is not correct. It states the object variable or with block has not been set.

Thanks in advance for the help