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

  • Thread starter Thread starter Fan924
  • Start date Start date
F

Fan924

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?
 
hi
what are you trying to do?
what error are you getting?
what value do you want range("A4:A19") to equal?

regards
FSt1
 
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.
 
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
 
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.
 
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.
 
(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
 
Back
Top