Macro Error

[rajltd]

HI,

I have written a marco in excel but with following code i have a problem.

For Each h In Range(ws.Cells(1, 1), ws.Cells(1, .Column.Count + .Column - 1))

This is part of the marco, which will select for the particular 1st row to find something to do something. But I am getting this error on one pc and not on the other...????

Run time error: 424
Object required

Can someone tell me how to debug this error???

Any help is much appreciated

Raj

 

[rdodge]

I would need to see more of the code to be sure, but it looks from what little I have to go by, the code may be trying to refer to an object that either doesn't exist or have a bad reference to the object.

Another way of how this error may happen, if the active sheet is a Chart Sheet, the Range Object could error out cause it's not prequalified by a worksheet object and by default, if there is no prequalifying object in front of the range object, it will take the active worksheet, if there is one active, but will error out otherwise.

What you can do, on the PC that it's erroring out, go into the VBE Window, put in a break point, then run the code which then it will break out at that point, then you can step through as you watch the different variables/objects/properties within the Watch Window.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rajltd]

Thanks ronald for your reply.

This is the complete code i am working on.

Option Base 1

Sub FixedColumnWidth()

Cells.Select

Dim shArr() As Variant
ReDim shArr(Sheets.Count)
For i = 1 To Sheets.Count
shArr(i) = Sheets(i).Name
Next i
Sheets(shArr()).Select


Selection.ColumnWidth = 8

For Each ws In Worksheets

With ws.UsedRange


For Each h In Range(ws.Cells(1, 1), ws.Cells(1, .Column.Count + .Column - 1))


If h.Value = "Accessory" Then
h.EntireColumn.ColumnWidth = 50


End If

Next

End With

Next
.........some formating code

End Sub


This is the complete code i am working on..

Hope this will help

Raj

 

[rdodge]

The one thing I don't see is the 'h' variable being declared as a range like:

Dim h As Range

It's good practice to get into declaring all of your variables. Granted, some works out, but for the 2 reasons of making the application work more efficient of using less memory in most cases, and to not allow for near as much of a confusion or chance of the code declaring the variable as a data type variable that you didn't want it declared as. One other thing to also get into good practice is to set the variable name with a meaningful name, which involves 2 parts, first part would be to use a conventional code to descript what type of object (again, to help VBA from getting confused from one type of object with another type of object that may otherwise had the same name, even when in different modules), and the rest of the name to be basically what the data type variable is. Example:

Dim rngCurHdr as Range, rngRowHdr as Range

In the above example, you see each of the 2 variables are declared as range objects, and just by looking at the varibale names, you would know this, which helps greatly for debugging purposes, the first one you know it's for which ever header is currently being evaluated, and the other one is the whole range of the column header. The variable names are also still kept relatively short so as to make the code a little more efficient.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rajltd]

This hasnt solved the problem.... I think it has something to do with the macro setting.. as it works on one system and doesnt on another... any ideas???

Raj

 

[rajltd]

I think, in the VBAProject(PERSONAL.XLS) the macros that we write are under the module "Modules". If i can override the "Microsoft Excel Objects" module over by "Module" module in this "VBAProject(PERSONAL.XLS)" class module then I will be able to run the code... Does anyone have any idea how to do that without losing the MS Excel objects.

Thanks

Raj

 

[rajltd]

Can anyone help pls

Raj

 

[dyarwood]

rajltd

You say the macro works on one computer but not on others is that right?

Are the macros recorded into the PERSONAL.XLS spreadsheet. If so this spreadsheet will only be available to the computer it is on. If you want to use the macros for this workbook on other computers it has to be recorded under the actual workbook being used.

have I got the right idea or am I way out?


dyarwood

 

[rajltd]

Well I have this into the personal workbook. But I copy this code into another computer and copy in that PC's excel personal worksheet. So this should work... but the code is not working.

 

[rdodge]

Just a thought, but if multiple users are using this same startup workbook, could you store the file on the network (if you have one) in a folder that is only meant for Excel to start up the listed files within that folder and no sub folders in it?

Example:

\\NetworkFileServer01\Production\XLStartup

Then have this workbook put in the XLStartup folder, but only under a different name such as "ProductionSU.xls"

How this would be utilized, you can set the Alternative Startup Location under Tools>Options>General, which then Excel will start up any files or shortcuts to files in that particular folder. This way, you don't have multiple copies floating around on the individual PCs as this would not be necessary unlike what would be necessary with the FE part of an Access DB program.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.