Looping thru Sheets

[bubba100]

The following bit of code loops through each sheet in an Excel workbook. When/if it finds a specific sheet it looks in a range to see if some text is there. If the text is not there it adds text,forumlas are inserted in certain ranges and other "stuff". What is happening it will do what I thought I asked it. But it also edits one other sheet. This other sheets name is not close to the one I want. I am using Access97 to control Excel2003. Does anyone have any ideas?

For i = 1 To EXFile.Application.Sheets.Count
strname = EXFile.Application.Sheets(i).Name
If ("IRP_Form" & VarNum) = EXFile.Application.Sheets(i).Name And IRPAudYr = Right(RST!IRPYrs, 2) And _
EXFile.Application.Sheets(i).Range("R14").Value <> "Page Description:" Then
EXFile.Application.Sheets(i).Activate
EXFile.Application.ActiveSheet.Range("S14:S16").Value = ""
EXFile.Application.ActiveSheet.Range("S19:S23").Value =

 

[SkipVought]

Hi,

This ought to work

    With EXFile
        For i = 1 To .Sheets.Count
            strname = .Sheets(i).Name
            If ("IRP_Form" & VarNum) = .Sheets(i).Name And _
                IRPAudYr = Right(RST!IRPYrs, 2) And _
                .Sheets(i).Range("R14").Value <> "Page Description:" Then
                    With .Sheets(i)
                        .Range("S14:S16").Value = ""
                        .Range("S19:S23").Value = ""
                    End With
        
        
            End If
        Next
    End With

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

Thanks Skip for the reply. In the past you have helped more than once with replies to both myself and to others with the same "problem" I was having.

I modified my code to reflect your suggestion. Unfortunately I got the same results.

I have looked futher and "think" I might know where the problem is. With the Excel file open I went to the VBA code. It lists the various sheets in the file. The "offender" is Fuel listed there as Sheet7 and IRP_Form1 as Sheet11. When I stepped thru the code in Access the i for Fuel was 11 and the i for IRP_Form1 was 18. I don't know however to take care of this.

For i = 1 To EXFile.Application.Sheets.Count
strname = EXFile.Application.Sheets(i).Name
If ("IRP_Form" & VarNum) = EXFile.Application.Sheets(i).Name And _
IRPAudYr = Right(RST!IRPYrs, 2) And _
EXFile.Application.Sheets(i).Range("R14").Value <> "Page Description:" Then
'EXFile.Application.Sheets(i).Activate
EXFile.Application.Sheets(i).Range("S14:S16").Value = ""
EXFile.Application.Sheets(i).Range("S19:S23").Value =

 

[SkipVought]

Sheet Index is merely the physical ORDER of the sheets in the workbook.

I would DEFINITELY NOT depend on the sheet order since this can be quite easily changed by ANY user.

A Sheet Name is the Tab Name. This value TOO, can be changed by the user.

Rather, I use the Sheet CodeName. It can ONLY be assigned either in the VB Editor or via VB code. This is the ONLY way to be absolutely sure of the sheet that you are referencing.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

Skip thanks for the explaination.

I added a variable for the CodeName and stepped thru again. I can watch it go to the sheet I asked it to AND the one I don't. I tried changing the if statement to

If EXFile.Application.Sheets(strname).Name = "IRP_Form" & VarNum And _
IRPAudYr = Right(RST!IRPYrs, 2) And _
EXFile.Application.Sheets(strname).Range("R14").Value <> "Page Description:" Then

to no avail. It must be "something" in the if statement that is causing it to misread on that one sheet, but what I don't know (yet).

 

[SkipVought]

But, you are NOT using the CodeName in the code that you posted.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

This will be part of a running update that is used by 50 people on laptops. The sheet I am looking for may or may not be in the workbook. There is not a specific order in which the sheets are added (only when needed). So the way I understand CodeName, the sheet is given a name when created (we set the Name but never CodeName-didn't know about it). On one file the sheet I am looking for might be Sheet10 while on another it might be Sheet15, that is why I am trying to use Name.

I am not a newbie to Excel but there is much I don't understand. So any pointers are GALDLY accepted.

 

[SkipVought]

When a sheet is added, both the Name and CodeName are assigned by Excel as unique names.

That's not the point. The only way that the CodeName can be changed is via VB.

Any Tom, Dick or Harry can change sheet Names & sheet order. So how can you be sure WHAT you are looking for unless you know something that does NOT change?

You also state the the sheet you are looking for may or may not be in the workbook. I guess I don't understand the environment that you are working in.

In an uncontrolled environment, where anything can change and nothing is constant, how can you know anything with certainty?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

Your points are well taken.

Let me try and explain.
We have 50 people each working with multi clients. Each client has their own seperate db and excel workbook. each client is different, some have need for sheet IRP_Form1 some don't. So its added as needed.

What I am trying to doing is to loop thru all the different sheets if it finds IRP_Form1 (there might also be IRP_Form2 and IRP_Form3) it looks futher to see if some text is in a certain cell. If the text is there then it does nothing, if not the code does several "things". This the code does. what it is also doing when it loops to sheet Fuel is add the text.

I use this same basic code to loop thru looking for a specifc sheet Name if not there it adds and this works All the time.

 

[SkipVought]

for each ws in worksheets
  with ws
    select case left(.name,3)
       case "IRP"
         if .[R14] <> "Page Description:"  then

         end if
       case else

    end select
  end with
next

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

thanks Skip I'll give it a try.
I normally don't do much with Excel and the person who developed the workbook is unavailable.

 

[SkipVought]

What does

...And IRPAudYr = Right(RST!IRPYrs, 2) And...

contribute?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[bubba100]

it helps determine that I am looking at the correct sheet. In the past we only allowed IRP_Form1,2and3. Now by adding to the sheet name IRP_Form1_03 we can have an unlimited (not really but more than currntly available). What you sent might work because on the new format sheets the text I am trying to add to the old format sheet is already there.