How to select a worksheet based on text in cell A1?? 1

[rlee16]

I am running a batch file, where I open up files which have 7 worksheets on average. I want to make changes on a particular sheet where in cell A1 contains the text "xxxxxx detailed". (where xxxxxx represents varying text).

I would like my code to open up the file; search through Cell A1 on each wkshts and when it finds the one with "detailed" on it, to Select that wksht.


Do Until mcol = z

Workbooks.Open Filename:="S:\Data\1Comp\" & Cells(603, mcol), _
UpdateLinks:=0
MyName1 = ActiveWorkbook.Name

'Search thru wkshts
'If Range("a1&quot = ****detailed??? Then
Current Worksheet.Select

Thanks for the help.

Richard

 

[TonyJollans]

Hi Richard,

Try ...

Dim wks as Worksheet

For Each wks In ActiveWorkbook.Worksheets

    If Right(wks.[A1], 8) = "detailed" Then

        ' Do your stuff here

    End If

Next

Enjoy,
Tony

 

[rlee16]

Tony,

I have added in the code by it seems to skip right over the If Right(wks.[A1], 8) = "detailed" part.

Is the [A1] coding correct?

is there a way to not use the trim function, instead use a find? or does the *.* syntax work in VBA?

Thanks

Richard

 

[TonyJollans]

Hi Richard,

When you say skips right over do you mean it is entirely bypassed (the loop doesn't even run once)? Or does it just not match the cell?

The [A1] format should be fine but if you want to code wks.Range("A1&quot as per your original post that's good too.

You can say If wks.[A1] Like "*detailed" if you prefer.

Enjoy,
Tony

 

[rlee16]

Thanks Tony,

I was using F8 to run through the program and I didn't let it run through the loop once.

it works great!

Richard