VBA Statements work in debug, but not otherwise.

[bassnsjp]

I'm using MS Office 2003 with an OS XP PRO.

I do not have access to the PC in which I'm developing the macro on so I have to re-type it here so please bare with me (there may be a typo or two). Anyway what I'm doing is setting up and formatting column headings for required and available inventory information. Formatting consists of merging and centering cells, and setting boarders. I have commented out some statements below because when I debug the code by stepping through them line by line they execute and the desired results are produced. However, when I try to run it not in debug the control of the macro returns to the calling subroutine once the process reaches anyone of these statements. Why will they execute in debug and not otherwise? How can I get the desired results in an operational mode? Any assistance in this matter would be greatly appreciated.

[VBA]
Sub ABC()

Call XYZ

End Sub


Sub XYZ()
etc
etc

Worksheets(CURwksname).Cells(3, 1) = “Part No.”
Worksheets(CURwksname).Cells(3, 2) = “Description”
Worksheets(CURwksname).Cells(3, 3) = “Qty”
Worksheets(CURwksname).Cells(3, 4) = “Qty”
Worksheets(CURwksname).Cells(3, 5) = “Description”
Worksheets(CURwksname).Cells(3, 6) = “Part No.
With Worksheets(CURwksname).Range(“A1:A” & Rows.count)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With Worksheets(CURwksname).Range(“C1” & Rows.count)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
With Worksheets(CURwksname).Range(“F1:F” & Rows.count)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Worksheets(CURwksname).Cells(2, 1) = “Required Inventory”.
Worksheets(CURwksname).Cells(2, 4) = “Available Inventory”

(If any statement below is not commented out control will return to ABC)
‘ Worksheets(CURwksname).Range(”A1:F1”).Select
‘ Selection.Merge
‘ Worksheets(CURwksname).Range(”A2:C2”).Select
‘ Selection.Merge
‘ Worksheets(CURwksname).Range(D2:F2”).Select
‘ Selection.Merge
‘ Worksheets(CURwksname).Range(A1:G3”).Select
‘ Selection.Borders(xlDiagonalDown).LineStyle = xlNone
‘ Selection.Borders(xlDiagonalUp).LineStyle = xlNone
‘ With Selection.Borders(xlEdgeLeft)
‘ .LineStyle = xlContinuous
‘ .Weight = xlMedium
‘ End With
‘ With Selection.Borders(xlEdgeLeft)
‘ .LineStyle = xlContinuous
‘ .Weight = xlMedium
‘ End With
etc
etc

End Sub
[/VBA]

[Simon Lloyd]

If you are selecting cells on a worksheet you must have that as your activesheet you would be better off using a with statement i.e
[vba]
With Worksheets(CURwksname)
.Range(”A1:F1”).Merge
etc.....

[/vba]

[mdmackillop]

[VBA]
Sub XYZ()
Dim CURwksname As String
CURwksname = "Sheet1"
With Worksheets(CURwksname)
.Cells(3, 1).Resize(, 5) = Array("Part No.", "Description", _
"Qty", "Qty", "Description", "Part No.")
With .Range("A:A,C,F:F")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
End With
End Sub

[/VBA]