Sheets(s).Range("A4:A19").Select error
 

I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

hi
what are you trying to do?
what error are you getting?
what value do you want range("A4:A19") to equal?

regards
FSt1

"Fan924" wrote:

I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

Not really sure I understand what you are trying to achieve bu the following
might help.

No need to select for what you appear to be doing. Simply apply the number
format to the range in the one line of code.

Sheets(s).Range("A4:A19").NumberFormat = "0.00%"

You can't ask for the value of a range because all cells in a range might
not be the same value and the code would need to return the individual cells
and values. However, you can set a range to all the same value like this.
Range("B1:B10").Value = 30

If not helpful then a little more explanation of what you are trying to
achieve.


--
Regards,

OssieMac


"Fan924" wrote:

I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

what ossiemac posted is correct. also, you're getting the error because you
cannot do a selection on a sheet that's not the active sheet. but not need
to select the sheet 99% of the time. you also don't need the s after next.
here is another way to write it:

Sub test()
Dim s As Long
For s = 2 To ActiveWorkbook.Sheets.Count
With Sheets(s)
If .Range("A50").Value = 3 Then
.Range("A4:A19").NumberFormat = "0.00%"
End If
End With
Next
End Sub





--

Gary Keramidas
Excel 2003


"Fan924" wrote in message
...
I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

Hi Gary,

"you also don't need the s after next". It may not be needed but my personal
opinion is that it is wonderful documentation in the code; especially if you
have nested loops it makes it so much easier to follow.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

what ossiemac posted is correct. also, you're getting the error because you
cannot do a selection on a sheet that's not the active sheet. but not need
to select the sheet 99% of the time. you also don't need the s after next.
here is another way to write it:

Sub test()
Dim s As Long
For s = 2 To ActiveWorkbook.Sheets.Count
With Sheets(s)
If .Range("A50").Value = 3 Then
.Range("A4:A19").NumberFormat = "0.00%"
End If
End With
Next
End Sub





--

Gary Keramidas
Excel 2003


"Fan924" wrote in message
...
I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

not sure about this, but i kind of remember a discussion, too, where someone
said it may slow down execution. i think tom Ogilvy was in it. but i could
be mistaken. just a thought.

--

Gary Keramidas
Excel 2003


"OssieMac" wrote in message
...
Hi Gary,

"you also don't need the s after next". It may not be needed but my
personal
opinion is that it is wonderful documentation in the code; especially if
you
have nested loops it makes it so much easier to follow.

--
Regards,

OssieMac


"Gary Keramidas" wrote:

what ossiemac posted is correct. also, you're getting the error because
you
cannot do a selection on a sheet that's not the active sheet. but not
need
to select the sheet 99% of the time. you also don't need the s after
next.
here is another way to write it:

Sub test()
Dim s As Long
For s = 2 To ActiveWorkbook.Sheets.Count
With Sheets(s)
If .Range("A50").Value = 3 Then
.Range("A4:A19").NumberFormat = "0.00%"
End If
End With
Next
End Sub





--

Gary Keramidas
Excel 2003


"Fan924" wrote in message
...
I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?

Sheets(s).Range("A4:A19").Select error
 

(1) s after next is redundant, and (2) you can't select a range if the sheet
that the range is on isn't active, but in this case, it doesn't need to be.
Here's the amended code for you:

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").NumberFormat = "0.00%"
End If
Next

"Fan924" wrote in message
...
I am looping through sheets "s" 2 to 20

For s = 2 To ActiveWorkbook.Sheets.Count
If Sheets(s).Range("A50").Value = 3 Then
Sheets(s).Range("A4:A19").Select
Selection.NumberFormat = "0.00%"
End If
Next s

This works fine ... Sheets(s).Range("A50").Value

Sheets(s).Range("A4:A19").Select.... When I change .Select to.Value ,
I get an error. What can I use as an alternate?