If Worksheet Visible? 2

[VictoriaLJones]

Hi there,

I am trying to assess the worksheet that the macro should obtain a cell value from. Basically the Workbook in question could have a number of hidden worksheets at the beginning, either 1 or 2. The information I need is stored in the first visible worksheet.

As I not that comfortable with VBA yet, I have cobbled together the following:

wkBookRec.Activate
Set wkSheet = wkBookRec.Worksheets.Item(i)

Do Until wkSheet(i).Visible = True
For i = 1 To wkBookRec.Worksheets.Count
If wkSheet(i).Visible = True Then
Set wkSheet = wkBookRec.Worksheets.Item(i)
Else
End If
i = i + 1
Loop


I am currently getting "Compile Error - Loop without Do". What is it I am doing wrong - or am I on totally the wrong track?!

Thanks
Victoria

 

[SQLBI]

Victoria,

This is a quite misleading error message as you do indeed have a 'Do' and 'Loop'.

The problem is in your For statement, which needs a 'Next' element.

Try this:

Do Until wkSheet(i).Visible = True
     For i = 1 To wkBookRec.Worksheets.Count
        If wkSheet(i).Visible = True Then
            Set wkSheet = wkBookRec.Worksheets.Item(i)
         Else
      End If
     Next i
  i = i + 1
  Loop

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[SQLBI]

... Sorry, delete the

i = i + 1

also.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[chandlm]

Why not try


For Each sheet In wkbookrec.Worksheets
If sheet.Visible = True Then
Set wksheet = sheet
Exit For
End If

Next

HTH [santa2]

Matt

 

[VictoriaLJones]

Thanks for that.

Both solved the error - thanks.

Actually used Matts answer though as it got round another "subscript out of range" error I was experiencing. Clearly trying to be too clever for my own good!

Thanks again!
Victoria

 

[chandlm]

Glad it helped.
Thanks for the STAR



Matt