For Each

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

What is the value of w in this case?

For Each w In Worksheets
If DataSheet = w.Name Then
Exit For
Else
If w = Sheets.Count Then
MsgBox "The name entered does not match any sheet in
this workbook. Please try again.", vbCritical, "No Worksheet Found With That
Name"
End If
End If
Next

My code fails at "If w = Sheets.Count then" I'm assuming it's not a number.
I can get w.Name to return a value, but how do I tell what the sheet number
is?

Thanks,

Brad
 
Hi,
w has a reference to Worksheet object, which does not have count property,
so your code fails

I think your code should look like:

dim fFound as Boolean

For Each w In Worksheets
If DataSheet = w.Name Then
fFound=true
Exit For
End If
Next

If not fFound Then
MsgBox "The name entered does not match any sheet in
this workbook. Please try again.", vbCritical, "No Worksheet Found With That
Name"
End If
 
Not sure what you're doing but w is the worksheet object. Try;

If w.Index = Sheets.Count Then

So you'll be comparing long to long

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for taking the time to read my question.
|
| What is the value of w in this case?
|
| For Each w In Worksheets
| If DataSheet = w.Name Then
| Exit For
| Else
| If w = Sheets.Count Then
| MsgBox "The name entered does not match any sheet in
| this workbook. Please try again.", vbCritical, "No Worksheet Found With
That
| Name"
| End If
| End If
| Next
|
| My code fails at "If w = Sheets.Count then" I'm assuming it's not a
number.
| I can get w.Name to return a value, but how do I tell what the sheet
number
| is?
|
| Thanks,
|
| Brad
 
Is the default for fFound False?

Alex Dybenko said:
Hi,
w has a reference to Worksheet object, which does not have count property,
so your code fails

I think your code should look like:

dim fFound as Boolean

For Each w In Worksheets
If DataSheet = w.Name Then
fFound=true
Exit For
End If
Next

If not fFound Then
MsgBox "The name entered does not match any sheet in
this workbook. Please try again.", vbCritical, "No Worksheet Found With That
Name"
End If
Click to expand...
 
Never mind. I just noticed that what fFound is set to doesn't matter.

Thanks for the help.

Brad
 
For Each w In Worksheets
If DataSheet = w.Name Then
Exit For
Else
If w = Sheets.Count Then
MsgBox errMessage
End If
End If
Next
Click to expand...

I don't think that For Each promises to go in Index order, so the inner
If is not guaranteed to do what you think it will do;

In any case, what about:

itsThere = True
On Error Resume Next
Set w = Sheets(DataSheet)
If Err.Number <> 0 Then itsThere = False


Hope that helps


Tim F
 
Back
Top