Macro Error on Cell.Select

[dcliffo]

The following macro works fine on my machine, but when I copy it to another co-worker's machine, I get a "Select method of range Class failed." It also worked fine on the co-worker's machine last Wednesday and now causes the above error. Help

Sub KDollars()
Dim rngBig As Range, rngCell As Range

Set rngBig = Range("C11:AO" & Range("AO65536").End(xlUp).Row)
ActiveSheet.Unprotect

For Each rngCell In rngBig.Cells
rngCell.Value = rngCell / 1000
Next rngCell
rngBig.NumberFormat = "0.0"

With Selection.Font
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$10"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&8&P"
.RightFooter = "&8&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = 95
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
End With
Cells.Select
Selection.Columns.AutoFit
Selection.RowHeight = 20
End Sub

 

[kkknie]

If the code is bombing on Cells.Select, it should not have anything to do with printing...I think.

Every time I have seen this error, it was because the active sheet or active window was not a spreadsheet and therefore did not have the Cells range to select. For me, this was normally caused by having a Chart sheet or chart as the active window and/or running the code from the VBE.

How are you running the code? From the Macro toolbar? From a Button?
Is there any other code that runs before this?

It may be possible to fix simply by selecting your sheet first:

Sheets("YourWorksheet").Select
Cells.Select

Very perplexing...

K

 

[dcliffo]

What we do is copy a file from an Oracle DB, then I paste into an Excel spreadsheet and run the macro from the Macro toolbar. I have copied the macro into a Personal.xls file that is run when Excel starts up. I hide this worksheet so the user must open a 'Book 1' spreadsheet to paste the spreadsheet. I have copied the Personal.xls spreadsheet into the XLStart folder on my computer and the others so that it shows up every time Excel is started. Am I doing this wrong?

 

[kkknie]

Your setup seems to be just fine.

Are these the steps?

The user runs a query or something on Oracle (SQLPlus?).
The user opens Excel.
The user opens a new workbook.
The user copys the data into the workbook (Sheet1?) via Copy/Paste.
The user selects the macro from the toolbar.

If so, I cannot see the problem.

Some thoughts:

If you remove the last three lines (from Cells.Select through Selection.RowHeight = 20),
does the code work? If not, I'm headed down the wrong path...

If it does, keep the last lines deleted, run the macro and then do the
last three steps manually (and record them). Then paste them back
into your macro.

Other than that, I'm baffled.

K

P.S. If you are getting the data through SQLPlus, you could eliminate the cut and paste
stuff by using an ADO database call. Let me know if you would like more info on that.

 

[dcliffo]

Haven't had a chance to sit down with the programmer to see what the back end looks like. I doubt he used SQL because you aren't able to query the DB (ugh). The reports are pre-built so the only way we can get them to look decent and readable is to copy and paste into Excel which defeats the whole purpose of having a relational DB...don't get me started. Anyway I'm trying a workaround the CELL.SELECT and that seems to work. However, now the macro does not divide each of the cells by 1000 to get K dollars.

Here is the revised code:

Sub KDollars()
Dim rngBig As Range, rngCell As Range
Rows("1:1").Select
Dim R As Long
Dim C As Range
Dim n As Long
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For R = rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
rng.Rows(R).EntireRow.Delete
n = n + 1
End If
Next R
rng.RowHeight = 20

EndMacro:


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic



Set rngBig = Range("C7:AO" & Range("AO65536").End(xlUp).Row)
ActiveSheet.Unprotect
For Each rngCell In rngBig.Cells
rngCell.Value = rngCell / 1000
Next rngCell
rngBig.NumberFormat = "0.0"

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$6"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&8&P"
.RightFooter = "&8&D"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = 95
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 4
End With
rng.Columns.AutoFit

'Cells.Select
'Selection.Columns.AutoFit
'Selection.RowHeight = 20

End Sub

I would like some more info on the ADO DB call though.....thanks!!!

 

[kkknie]

Not sure why the /1000 doesn't work. If there are any non-numeric entries in the range, you'll get an error and if you handle it, it may not work. But it doesn't seem like that's the case.

Use the immediate window to see what's going on.

For Each rngCell In rngBig.Cells 
debug.print rngCell.Value
rngCell.Value = rngCell / 1000 
debug.print rngCell.Value
Next rngCell

This should give some clues... Also, I'd use rngCell.Value / 1000 rather than rngCell / 1000. Both should work since value is the default property, but I know in VB.Net, you have to specify. Just good practice.

As for ADO and DB Admins, don't get ME started. My suspicion is that the entire Oracle DBA group is simply a covert organization to undermine the productivity of the workforce while milking companies for all they can get (as well a making up more rules and acronyms than you can count).

ADO is a simple way to query a database using either SQL or pre-defined queries. Here is a simplified example of one I wrote to an Oracle DB.

Public Sub DBAccess()

Dim objComm As ADODB.Command
Dim objRS As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String

strConnect = "DRIVER={Oracle in DEFAULT_HOME};DBQ=MOUSE.GWD;UID=USERA;PWD=PASSA"

Set objComm = CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect

strSQL = "Select * From tblMain Where Main_User = 'ABC';"

objComm.CommandText = strSQL
objComm.CommandType = adCmdUnknown

Set objRS = objComm.Execute

'--Example of looping through the dataset.
Do Until objRS.EOF
   msgbox objRS("Main_UserName")
   objRS.MoveNext
Loop

'--Example of pasting to a range
objRS.MoveFirst
Range("A1").CopyFromRecordset objRS

Set objRS = Nothing
Set objComm = Nothing

End Sub

If you have the client software installed, know the driver name, DB Name, UID and password, you're golden. Same thing can be done with an access database (which I do tons of).

K

 

[dcliffo]

Thanks for the sample code. Like I said haven't had a chance to get with the programmer to see the back end of the Oracle DB. We already had a local Access DB that could have been uploaded to a SQL Server which would have been a whole lot easier than starting from scratch, but I have a boss who is not very knowledgeable about Access, SQL OR Oracle so had a guy from India start from scratch with little or no input from the users. Now he has gone back to India so I am trying to just get some readable reports out of the DB. He created reports, but they are so small you can't read them easily....thus my dilemma. I've got everything working now EXCEPT the $K calculation. It doesn't perform this block of code. I've tried moving it around thinking it may have been in the wrong place, but still doesn't work. I even tried JUST doing this block of code and nothing else, but it still does nothing. However, as usual it works fine on my machine

 

[dcliffo]

I think I have found the problem....How do I store a macro that I want to access any time I open a new workbook? I created a personal.xls in my startup folder and then hide the window, but I think that is creating the problem when I try and store this file on someone else's computer. Could that be it? I just copied the code and a paste file to manipulate and tried it yet on another computer and it worked fine IF I pasted the macro code within that spreadsheet and ran it from there instead of using the personal.xls file. Does that make sense?

 

[dcliffo]

Well I think I found out why it wasn't working.....geez....I needed to copy the macro into a MODULE

YEH!!!!!

I can finally get some sleep.