worksheet variable not working

[turtlejack]

This code isnt working.. I want to make all of the cells in the selected range(using the variables of course)formatted to the bold and underlined font.but, I keep getting an error. any help would be appreicated..


Sub projectamend()

Dim num2 As Worksheet
Dim p As Long
p = 20
Set num2 = Worksheets("2")

Sheets(num2).Range("a5", "a" & p).Select
Selection.font.Bold = True
Selection.font.Underline = xlUnderlineStyleSingle
With Selection.font
.Name = "Times New Roman"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With

End Sub

 

[SkipVought]

Hi,

num2 has been set to a sheet object. This line is an incorrect use of num2:
[tt]
Sheets(num2).Range("a5", "a" & p).Select[/tt]

Sub projectamend()

    Dim num2 As Worksheet
    Dim p As Long
    p = 20
    Set num2 = Worksheets("2")
    
    With num2.Range("a5", "a" & p)
         .Font.Bold = True
         .Font.Underline = xlUnderlineStyleSingle
        With .Font
            .Name = "Times New Roman"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleSingle
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Zathras]

Change these 2 lines:
[tt]
Set num2 = Worksheets(2) ' No quotes
num2.Range("a5", "a" & p).Select ' num2 is a worksheet
[/tt]
And be sure the range A5:A20 is not empty.

 

[SkipVought]

Z....

Unless he has a worksheet named 2, which is what I assumed.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[Zathras]

Hey Skip, long time no hear...

Jack, Worksheets("2") is correct if your sheet is named "2" - you didn't specify. Use 2 (without quotes) to get the second sheet in the book (order of tabs). But be careful -- the user can change the tab order and then the macro might work on the wrong sheet. Better to use sheet names. (Of course, the user could change the sheet name too, but that's another story.)

Also, "num2" is not the best name for a worksheet object. -- Leads to confusion.

One minor point, it is not necessary to use nested "With" groups. (But it doesn't hurt, either).

 

[Zathras]

Skip, once again the fastest draw in the west.

 

[turtlejack]

actually I named my first 2 sheets in numerical order.so,in fact, the number 2 worksheet ,is "2". I did this to keep from writing long words..once I get my code right, I will be using these codes in a workbook(not yet created)with good working code.. hope that made sense.

 

[SkipVought]

West of The Red River, Bucko! ;-)

tj, Take a look at the CodeName of a sheet. This can ONLY be changed in VB, if there's a concern as Z has suggested.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[turtlejack]

ok just for the sake of clarity, I changed the worksheet name below.its the only thing I changed on here..
is this correct syntax? or what do I need to change. I know of another way to achieve this, but I wanted to use the variable because I am using it in some more code that will be in this sub...I didnt want to type out the sheet name over and over..


Sub projectamend()

    Dim num2 As Worksheet
    Dim p As Long
    p = 20
    Set num2 = Worksheets("inventory")
    
    Sheets(num2).Range("a5", "a" & p).Select
         Selection.font.Bold = True
         Selection.font.Underline = xlUnderlineStyleSingle
    With Selection.font
        .Name = "Times New Roman"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleSingle
        .ColorIndex = xlAutomatic
    End With

End Sub

 

[turtlejack]

ok I found the answer.. i did a search for help on codename. and got this.. was most helpful.


faq707-4090


thanks for all your help

 

[Zathras]

... which is exactly what I told you. So your code should now look something like this (If it doesn't, you might want to study the differences until you understand them -- if you want to learn VBA.)

Option Explicit

Sub projectamend()
Dim num2 As Worksheet
Dim p As Long
    
   p = 20
   Set num2 = Worksheets("inventory")
    
   With num2.Range("a5", "a" & p).Font
     .Bold = True
     .Underline = xlUnderlineStyleSingle
     .Name = "Times New Roman"
     .Size = 12
     .Strikethrough = False
     .Superscript = False
     .Subscript = False
     .OutlineFont = False
     .Shadow = False
     .Underline = xlUnderlineStyleSingle
     .ColorIndex = xlAutomatic
   End With

End Sub

Of course, if that's ALL you are going to do with the worksheet object, you don't need it at all. You can do the same thing this way: (The choice is yours.)

Sub projectamend2()
Dim p As Long
    
   p = 20
    
   With Worksheets("inventory").Range("a5", "a" & p).Font
     .Bold = True
     .Underline = xlUnderlineStyleSingle
     .Name = "Times New Roman"
     .Size = 12
     .Strikethrough = False
     .Superscript = False
     .Subscript = False
     .OutlineFont = False
     .Shadow = False
     .Underline = xlUnderlineStyleSingle
     .ColorIndex = xlAutomatic
   End With

End Sub

 

[turtlejack]

thank you for your help.. although, the suggestions you noted earlier were tochange the name(which I did, only as to not confuse what I was really trying to find out..)changing the name to inventory was just for clarity..The code still didnt work until I read faq I noted above.. the above example was not included in your earlier post or I wouldnt have had to look further .. The help I got was from the faq noted above..again, I do appreciate your participation