VBA question about Variables

[Sean Stuber]

I can't seem to get Serial, Model_Type, and Parking_Loc to hold a value.
Is there something wrong with this code?

Also, is there a command that will make the program do nothing...ie. I
used "beep" below but that's annoying.
----
Sheets("Report").Unprotect
strDate = Format(Date, "mm-dd-yy") & " " & Format(Time,
"h:mm:ss")
Worksheets("Report").Cells(1, 1) = "Inventory Report for " &
strDate
For Each Sh In Worksheets
If Sh.Name = "Start-Instructions" Or Sh.Name = "Log" Or Sh.Name
= "Report" Or Sh.Name = "NewReport" Then
Beep
Else

'\ Report on Inventory Data
Sheets("Report").Unprotect
Dim Serial As String
Dim Model_Type As String
Dim Parking_Loc As String
Row = Worksheets("Report").Range("Report_Value").Cells(1,
1).Value
Serial = Sh.Cells(4, 7).Value
Model_Type = Sh.Cells(4, 11).Value
Parking_Loc = Sh.Cells(4, 9).Value
Info1 = "Serial #" & Serial & " (" & Model_Type & ") "
Info2 = " is located at " & Parking_Loc
Worksheets("Report").Cells(Row, 1) = Info1 & Info2
Worksheets("Report").Activate
Columns("A:A").EntireColumn.AutoFit
Range("a1").Select
Sheets("Report").Protect
End If
Next Sh

 

[Alan Beban]

You might try

If Not Sh.Name = "Start-Instructions" And Not Sh.Name = "Log" And Not
Sh.Name = "Report" And Not Sh.Name = "NewReport" Then
'\ Report on Inventory Data
End If

Alan Beban

 

[Guest]

This does not solve your problem but the following line seems an eyeful

If Sh.Name = "Start-Instructions" Or Sh.Name = "Log" Or Sh.Nam
= "Report" Or Sh.Name = "NewReport" The

An alternative construction, if you are using Option Compare Text (this is the default) is

Select Case SH.nam
Case "Start-Instructions", "Log", "Report", "NewReport
Bee
Case Els
... your alternative cod
End Selec

 

[Harlan Grove]

You might try

If Not Sh.Name = "Start-Instructions" And Not Sh.Name = "Log" And Not
Sh.Name = "Report" And Not Sh.Name = "NewReport" Then
'\ Report on Inventory Data
End If

Some people like unnecessary typing.

If Sh.Name <> "Start-Instructions" And Sh.Name <> "Log" _
And Sh.Name <> "Report" And Sh.Name <> "NewReport" Then

Or, since worksheet names can't include colons,

If InStr(":Start-Instructions:Log:Report:NewReport:", _
":" & Sh.Name & ":") = 0 Then

 

[Tushar Mehta]

What do you mean by "hold a value?" Is there at least one sheet for
which you expect the Else part of the code to be executed? What is
that sheet named? Are there others?

I occasionally code If statements so that the Then part requires a
'nothing' statement. It turns out the easiest way to do nothing is
leave the statement empty. So, the following will work just fine:

If Sh.Name = "Start-Instructions" _
Or Sh.Name = "Log" Or Sh.Name = "Report" _
Or Sh.Name = "NewReport" Then
Else
'Else code
End If

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions