Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Select method of Range class failed" Error | Excel Discussion (Misc queries) | |||
"run-time error '1004' - select method of range failed" | Excel Programming | |||
VBA error 1004 "Select method of Range class failed" | Excel Programming | |||
Error 1004, "select method of range class failed" | Excel Programming | |||
Runtime Error "1004" Select Method of Range Class Failed | Excel Programming |