| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select....Case macro in Excel 2007
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select....Case macro in Excel 2007
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" wrote: 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 '========== -- Don Guillett Microsoft MVP Excel SalesAid Software "ManhattanRebel" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Select....Case macro in Excel 2007
Glad to help. Send your workbook to my address below and I can test.
-- Don Guillett Microsoft MVP Excel SalesAid Software "ManhattanRebel" wrote in message ... 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" wrote: 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 '========== -- Don Guillett Microsoft MVP Excel SalesAid Software "ManhattanRebel" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| Excel Select Case with HorizontalAlignment | Excel Programming | |||
| Calling Macro when using Select Case | Excel Programming | |||
| Excel Custom Function with Select Case | Excel Programming | |||
Hybrid Mode
