Select....Case macro in Excel 2007

[ManhattanRebel]

I am using this macro, taken from "Mr. Excel" Bill Jelen's book, which was
written in 2004. I have Excel 2007, and I don't know if something has
changed. I use Watch feature when debugging this. The expression "i" seems
to work, but nothing shows up in expression "Value". The debugger reads,
"<Expression not defined in context>. Any suggestions are appreciated.


Sub CopyPasteSurcharges()
'
'Macro to copy Surcharges from existing fields and move to new fields
'
Sheets("Ground").Activate
FinalRow = Cells(65536, 55).End(xlUp).Row

For i = 2 To FinalRow
Select Case Cells(i, 55).Value
Case "Additional Handling"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 79)
Case "Additional Weight"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 81)
Case "Address Correction"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 83)
Case "Adult Signature"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 85)
Case "Call Tag"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 87)
Case "COD"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 89)
Case "Courier Pick-Up Charge"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 91)
Case "Declared Value"
Range(Cells(i, 55), Cells(i, 56)).Copy
Destination:=Cells(i, 93)
End Select
Next i
End Sub

 

[Don Guillett]

I tested your code in 2007 workbook and it worked fine. I would have done it
like this.
BTW. If you do want to MOVE instead of just copy change copy to CUT

'=========
Option Explicit
Sub CopyPasteSurchargesDon()
Dim finalrow As Long
Dim i As Long
Dim mc As Long
'
'Macro to copy Surcharges from existing fields and move to new fields
'
With Sheets("Ground")
finalrow = .Cells(Rows.Count, 55).End(xlUp).Row
On Error Resume Next
For i = 2 To finalrow
Select Case .Cells(i, 55)
Case "Additional Handling": mc = 79
Case "Additional Weight": mc = 81
Case "Address Correction": mc = 83
Case "Adult Signature": mc = 85
Case "Call Tag": mc = 87
Case "COD": mc = 89
Case "Courier Pick-Up Charge": mc = 91
Case "Declared Value": mc = 93
Case Else
End Select

'.Range(Cells(i, 55), Cells(i, 56)).Copy .Cells(i, mc)
'or I used
.Cells(i, 55).Resize(1, 2).Copy .Cells(i, mc)
'to MOVE use this instead
'.Cells(i, 55).Resize(1, 2).CUT .Cells(i, mc)

Next i
End With
End Sub
'==========

 

[ManhattanRebel]

Thank you for your help.

I ran this macro after running some other macros that inserted new column
headings and froze panes, etc. Is it possible they intererfered with this
macro? Or can you think of a likely reason why it doesn't run?

Also, I use "copy' command, but after I run this macro on five different
columns, I plan to delete them and just leave the new columns.

ManReb

 

[Don Guillett]

Glad to help. Send your workbook to my address below and I can test.