ExcelBanter - Complie Error with xlValues

Hi Vincent,

As written, you will receive an error message if the search string is not
found on any worksheet or if the workbook contains sheets of non-worksheet
type, e.g. a chart sheet. Additional errors will be experienced because a
selection can not be made on a non-active sheet.

Addressing these issues, try:

'=============
Sub formatWorkbook(xlApp As Object, filename As String)
'format the workbook
Dim xlWkbk As Object
Dim sheet As Object
Dim i As Variant
Dim test As Variant
Dim TopLeftCell As String
Dim UsdRng As String
Dim BottomRightCell As String
Dim FilterRange As String
Dim Findfor As String
Dim rng As Range

Findfor = "Cust Master ID"
Set xlWkbk = xlApp.Workbooks.Open(filename) 'open the workbook

For i = 1 To xlWkbk.Worksheets.Count
Set sheet = xlWkbk.WorkSheets(i)
Set rng = sheet.Range("A:A").Find(what:=Findfor, _
LookIn:=xlValues)
If Not rng Is Nothing Then
TopLeftCell = rng.Address
UsdRng = sheet.UsedRange.Address
BottomRightCell = Right(UsdRng, _
Len(UsdRng) - InStr(UsdRng, ":"))
FilterRange = TopLeftCell & ":" & BottomRightCell
sheet.Range(FilterRange).AutoFilter
Application.Goto Reference:=sheet.Range("A1")
End If
Next i

xlWkbk.Save 'save the workbook
xlWkbk.Close 'close the workbook

Set xlWkbk = Nothing

End Sub 'formatWorkbook
'<<=============

FWIW, I would not use Sheet as a variable as I would find it very
confusing:: perhaps try a more conventional WS, wks or SH

---
Regards,
Norman

"vincent_vega"
wrote in message
news:vincent_vega.2azzxh_1152985808.3369@excelforu m-nospam.com...

Hi Folks,

I am new to the Whole VBA Programming and I am trying to insert a
autofilter condition in a row, when a particluar string in the
worksheet is identified.

All I am getting is an error in the Find method ,when I reach
LookIn:= xlvalues. It give me the following

compile error:
Variable not defined

I am not sure, how to fix this error. I tried various options, but I am
not able to fix this issue.

Here is the code i am working on:

Formula:
--------------------
Sub formatWorkbook(xlApp As Object, filename As String)
'format the workbook
Dim xlWkbk As Object
Dim sheet As Object
Dim i As Variant
Dim test As Variant
Dim TopLeftCell As String
Dim UsdRng As String
Dim BottomRightCell As String
Dim FilterRange As String
Dim Findfor As String

Findfor = "Cust Master ID"
Set xlWkbk = xlApp.Workbooks.Open(filename) 'open the workbook

For i = 1 To xlWkbk.worksheets.Count
Set sheet = xlWkbk.sheets(i)
TopLeftCell = sheet.Range("A:A").Find(what:=Findfor,
Lookin:=xlvalues).Address
UsdRng = sheet.UsedRange.Address
BottomRightCell = Right(UsdRng, Len(UsdRng) - InStr(UsdRng, ":"))
FilterRange = TopLeftCell & ":" & BottomRightCell
sheet.Range(FilterRange).AutoFilter
sheet.Range("A1").Select
Next i

xlWkbk.Save 'save the workbook
xlWkbk.Close 'close the workbook

Set xlWkbk = Nothing

End Sub 'formatWorkbook
--------------------

Any helps or suggestions would be appreciated.

Thank You.

--
vincent_vega
------------------------------------------------------------------------
vincent_vega's Profile:
http://www.excelforum.com/member.php...o&userid=36397
View this thread: http://www.excelforum.com/showthread...hreadid=561756