Solved: Range Select problem

[jwise]

I am using a "Sheets.Range.Select" in a routine. I wrote a test driver and this routine worked perfectly. Then I replaced my test driver with the real routine which cycles through all the worksheets in the workbook, and the Range.Select fails with a "1004". I do not understand why it would work before but not now.



wkSheet.Range("B3:M12").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

wkSheet.Range("B13:M14").Select
Selection.NumberFormat = "0.0%"

wkSheet.Columns("N:T").Select
Selection.Delete Shift:=xlToLeft




The error occurs on the first "Range.Select". This code is formatting all data sheets with print settings. The test driver only tested one worksheet, and it worked fine. The real routine fails on the first worksheet it attempts. Here is the test driver code that works:


Sub tst_FMT()

Call FMT(ThisWorkbook.ActiveSheet, 2007)

End Sub



As you can see, not very much code. Here is the real driver routine:


Sub FMT_Prt()

Dim i As Integer
Dim yr As Integer

Dim wkSheet As Worksheet

yr = InputBox("Please enter the YEAR of Data", "Input Data Year")

If yr < 70 Then yr = yr + 2000

For i = 1 To ThisWorkbook.Sheets.Count
If Len(ThisWorkbook.Sheets(i).Name) < 4 Then
Set wkSheet = ThisWorkbook.Sheets(i)
Call FMT(wkSheet, yr)
Set wkSheet = Nothing
End If
Next i

End Sub





I originally had the loop like this (same error):
For i = 1 To ThisWorkbook.Sheets.Count
If Len(ThisWorkbook.Sheets(i).Name) < 4 Then Call FMT(ThisWorkbook.Sheets(i), yr)

Next i




Any ideas what I've done wrong? TIA

[stanleydgrom]

jwise,

Try:




wkSheet.Range("B3:M12").NumberFormat = "#,##0_);[Red](#,##0)"

wkSheet.Range("B13:M14").NumberFormat = "0.0%"

wkSheet.Columns("N:T").Delete Shift:=xlToLeft




Have a great day,
Stan

[stanleydgrom]

jwise,

Or:




With wkSheet.Range("B3:M12")
.NumberFormat = "#,##0_);[Red](#,##0)"
End With

With wkSheet.Range("B13:M14")
.NumberFormat = "0.0%"
End With

With wkSheet.Columns("N:T")
.Delete Shift:=xlToLeft
End With




Have a great day,
Stan

[jwise]

Thanks Stan. I appreciate the assistance.

After looking at your solution, I do not understand why there is a difference in the outcome. You "traverse" the object heirarchy in one statement whereas my code descended in two statements. Why does it work when mine fails?

My question is to learn the principle behind this solution; i.e. to prevent a future occurence.

Thanks again.

[stanleydgrom]

jwise,

You should try and learn not to use "Select" (it can cause problems). And, your code will execute much faster.

Even though you used:
wkSheet.Range("B3:M12").Select
Selection.NumberFormat = "#,##0_);[Red](#,##0)"

You may want to examime the actual active worksheet to see if your code changed the number format of Range("B3:M12") in the actual active worksheet instead of "wkSheet".


Have a great day,
Stan

[Bob Phillips]

Thanks Stan. I appreciate the assistance.

After looking at your solution, I do not understand why there is a difference in the outcome. You "traverse" the object heirarchy in one statement whereas my code descended in two statements. Why does it work when mine fails?

My question is to learn the principle behind this solution; i.e. to prevent a future occurence.

Thanks again.

You cannot select a sheet and a range within that sheet within one statement. You have to activate the sheet, then select the range. If you are on another sheet, the double select should fail, if you are on that sheet it will work, but only because the sheet qualification is superfluous.

But of course, selecting is unnecessary and wasteful.

[jwise]

I really appreciate your insight.

The code above was my fourth attempt at solving my problem. The data was originally collected on a monthly basis for a set of properties, i.e. one worksheet in a workbook, for 42 properties. The added requirement was to produce charts with some additional information on a property-by- property basis, i.e. 42+ worksheets in one workbook (one for each property plus some additional data worksheets) with 12 months data on each property. Then I needed to produce three graphs for each property.

Because of deadlines and my inability to get this copy done, I manually created the first worksheet and got the chart functions to work. Then I copied all the data and processed all the charts. Since I met my deadline and I didn't want to ever have this marathon copy party again, I went back to the original problem: Take 12 workbooks on 42 properties in each, and convert the data into 42 worksheets in one workbook. The data is row-oriented in the monthly sheets, and in column format in the property worksheets.

I could make the worksheet copy/conversion work for one worksheet, but it would fail (three different implementations all failed in the same place) on the second property. So I began my search for a routine that would do this copy/conversion. From something I read previously, I was trying to avoid the use of "Paste", but I simply could not get this to work. I could have also use the "wkA.Cells(a, b) = wkB.Cells(x,y)" construct, but I wanted to avoid that as well. My theory was that "Cells(a, b)" was converted to some "object form" and less efficient, and I needed to learn the real way to do this with objects.

My original attempts were various forms of "Range" copy. I eventually got this to work, but it would not "transpose" the data properly. I could not find how to use the "Transpose:=True" with the Range copy. I did not find this with "Help" or "Google", so I abandoned this and moved to "Paste Special".

Could I have done this with a Range copy?