Hi Folks,

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

All I am getting is an error in the Find method ,when I reac
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 a
not able to fix this issue.

Here is the code i am working on:


PHP code
-------------------
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 'formatWorkboo
-------------------


Any helps or suggestions would be appreciated.

Thank You

--
vincent_veg
-----------------------------------------------------------------------
vincent_vega's Profile: http://www.excelforum.com/member.php...fo&userid=3639
View this thread: http://www.excelforum.com/showthread.php?threadid=56175

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