How to move values from 1 column to anothe column ? 1

[btull64]

The following procedure works fine the 1st time I run the procedure. This procedure reformats an Excel spreadsheet.

When I RERUN the procedure, then after I close the spreadsheet, the compiler stops with the following line highlighted:
For Each Cell In Range(xlWS.Cells(1, 16), xlWS.Cells(65536, 16).End(xlUp))

I get a Run time error '1004'
Method 'Range' of object '_Global' failed
---------------------------------------------------------
If I stop the compiler and rerun the procedure, it works fine again.

Do you know how I can avoid this Runtime error '1004' ?
Method 'Range' of object '_Global' failed
---------------------------------------------------------
The purpose of the For Next Loop is as follows:
If (and only if) there is a value in Column P, first move that row's value in column O to column A. Then move only that particular row's value in column P to column O.

---------------------------------------------------------


For example:
BEFORE


col col col
A O P
--- --- ----
25
36
sub total 61

27
46
sub total: 73
---------------------------------------------------------------------------------------
AFTER

col col col
A O P
----- ------- -----
25
36
sub total: 61

27
46
sub total: 73

-------------------------------------------------
Sub StartDetailUDLXLS(filename)
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim Cell As Excel.Range
'open excel template
Set xlApp = New Excel.Application
xlApp.Visible = True
xlApp.ScreenUpdating = False
Set xlWB = xlApp.Workbooks.Open(filename)
Set xlWS = xlWB.Worksheets(1)
Set xlWS = ActiveSheet

xlWS.Range("O4:O65535").NumberFormat = "###,##0.00"
xlWS.Range("B4").ClearContents
xlWS.Range("A5").ClearContents
xlWS.Columns.AutoFit
xlApp.Calculation = xlCalculationManual

For Each Cell In Range(xlWS.Cells(1, 16), xlWS.Cells(65536, 16).End(xlUp)) <----- compiler stops here
If Cell <> "" Then
Cell.Offset(0, -1).Cut Cell.Offset(0, -15)
Cell.Cut Cell.Offset(0, -1)
End If
Next


xlApp.Calculation = xlCalculationAutomatic
xlApp.ScreenUpdating = True
Set Cell = Nothing
Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

 

[GlennUK]

Can you explain what ( in this quote from you ):

When I RERUN the procedure, then after I close the spreadsheet, the compiler stops

the part then after I close the spreadsheet needs a bit of explanation ...you close the spreadsheet, then the compiler stops?????? Doesn't make sense.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[btull64]

I see your point of confusion. My Access application creates a spreadsheet and performs a procedure to reformat and reposition fields on this spreadsheet. After the spreadsheet is opened by my application, if I then close the spreadsheet, the following line in my Access application is highlighted in yellow:

For Each Cell In Range(xlWS.Cells(1, 16), xlWS.Cells(65536, 16).End(xlUp))

I get a Run time error '1004'
Method 'Range' of object '_Global' failed

 

[PHV]

Remove the following line:
Set xlWS = ActiveSheet

Replace this:
For Each Cell In Range(xlWS.Cells(1, 16), xlWS.Cells(65536, 16).End(xlUp))
with this:
For Each Cell In xlWS.Range(xlWS.Cells(1, 16), xlWS.Cells(65536, 16).End(xlUp))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886