Works on the trial verson of excel 2007 but not my student version

A

ACyberPoet

this is a weird one ...

I was playing around with the trial verson of Excel 2007 on my computer at
home and created a way to help my boss at work. When I put it on my laptop
(running the student verson of excel 2007) it worked fine the first couple of
times I ran it. When it came time to actually show it to the boss it kept
saying that there was a code break after each of my "IF" statements and when
I was haveing the program change the cell sizes... Any ideas why this would
run just fine on the trial version but not on the student one? ... If I can
get it to work and my boss likes it we will be running it on the Business
version, should I be prepared for more problems then?

Thanks in advance,
Gabriel
 
A

ACyberPoet

sorry its a rather long thing ... Heres one of the chunks that keep getting
errors ...

Worksheets("order").PageSetup.Orientation = xlLandscape
With Columns("A:A")
.NumberFormat = "0"
.ColumnWidth = 12.57
End With
Columns("B:B").ColumnWidth = 39.57
Columns("C:C").ColumnWidth = 29.14
With Columns("D:D")
.ClearContents
.ColumnWidth = 5.43
End With
Columns("E:E").ColumnWidth = 4.14
Columns("F:F").ColumnWidth = 4.71
With Columns("G:G")
.NumberFormat = "$#,##0.00"
.ColumnWidth = 6.71
End With
End Sub

in this one it errors on the first column width change...

Range("A1").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(0, 6).Select
If ActiveCell.Value = "" Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
Else: ActiveCell.Offset(1, -6).Select
End If
Loop
Range("A1").Select
End Sub

and this is one of the "IF" errors that I keep getting

any help is greatly appreciated
 
J

JLGWhiz

The first snippet does not tell me too much without knowing what the error
message was.

One thing that I notice in the second snippet is that you change the
ActiveCell in mid loop, then attempt to bring the cursor back to the
starting column. It is a little cumbersome, but I understand the logic.

This could be written as a For...Next loop to do what you want.

Dim lr As LOng
'Find last row with data in col A
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Set the limits for the range to check
For i = lr To 2 Step - 1
'Check value in Column G
If ActiveSheet.Cells(i, 7) = "" Then
Rows(i).Delete 'Delete the row
End If
Next
End Sub

This eliminates the Select method and cuts out the flicker and flash. This
assumes that column A has contiguous data beginning in row 2 for as many
rows until the last cell with data in that column. It works from the bottom
of the range to the top because the default shift is up and can cause skips
if it works downward. I mention this because your original code used Do
While value not "". That method would stop at the first blank cell, but the
For... next loop will not.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top