Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can anyone help me tidy up?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Can anyone help me tidy up?

Don't laugh! I've spent weeks doing this (my first proper macro!) and am
so pleased with it!

But, it's rather large.

Any tidying up possible?

Thankyou!


Code:
--------------------
Sub Pick()

'Open a CAP file
ChDrive "J"
ChDir "J:\Accounts\Cap\"
fname = Application.GetOpenFilename()
Workbooks.OpenText Filename:=fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) _
, TrailingMinusNumbers:=True

'Remember the CAP name
Dim CapName As Variant
CapName = ActiveWorkbook.Name



'Find the beginning of useful data
Cells.Find(What:="end of day report for day", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Get rid of all the junk data
Dim L As Long
L = Selection.Row - 3
If L 0 Then
s = "1:" & L
Rows(s).Select
Selection.Delete Shift:=xlUp
End If

'Add appropriate column seperators
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(23, 1), Array(32, 1)), TrailingMinusNumbers _
:=True

'Select all and copy to Workbook
Cells.Select
Selection.Copy
Windows("Stores.xls").Activate
Sheets("Temp").Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows(CapName).Close
Application.DisplayAlerts = True

'Extract the date
Range("F1").Select
ActiveCell.Formula = "=MID(A1,5,11)"
Range("F1").Select
Dim CapDate As Date
CapDate = Range("F1").Value

'Extract the Till number
Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"
Range("F2").Select
Dim TillNumber As Integer
TillNumber = Range("F2").Value

'Find the Time & Value data
Cells.Find(What:="timeband report", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim rng As Range

'Copy the Time & Value data
Set rng = ActiveCell.Offset(4, 1).Resize(24, 2)
rng.Copy

'In main workbook, find which column to paste data into
Windows("Stores.xls").Activate
Sheets("Till0" & TillNumber).Select
Range("A1").Select

'Set the first Zero row
Cells.Find(What:="0000", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Zero As Variant
Zero = ActiveCell.Row

'Set the first payment method row
Cells.Find(What:="Account Sales", LookIn:=xlValues, SearchFormat:=False).Activate
Dim AccSales As Variant
AccSales = ActiveCell.Row

'Set the first discount row
Cells.Find(What:="item discount", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Discounts As Variant
Discounts = ActiveCell.Row

'Search for the Cap Date
Cells.Find(What:=CapDate, LookIn:=xlFormulas, SearchFormat:=False).Activate
Dim CapDateCol As Variant
CapDateCol = ActiveCell.Column

'Paste the Till value and data
Worksheets("Till0" & TillNumber).Cells(Zero, CapDateCol).Select
ActiveSheet.Paste

'Lookup Payment Method data
Worksheets("Temp").Activate
Range("F10").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(1, 0).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(2, 0).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALS E))"
ActiveCell.Offset(3, 0).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(4, 0).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(5, 0).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,2,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,2, FALSE))"
ActiveCell.Offset(6, 0).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(7, 0).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(8, 0).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(9, 0).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(10, 0).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FA LSE))"
ActiveCell.Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(1, 1).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(2, 1).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALS E))"
ActiveCell.Offset(3, 1).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(4, 1).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(5, 1).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,3,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,3, FALSE))"
ActiveCell.Offset(6, 1).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(7, 1).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(8, 1).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(9, 1).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(10, 1).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FA LSE))"


'Copy Payment Method data to summary

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Value = Worksheets("Temp").Cells(10, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 0).Value = Worksheets("Temp").Cells(11, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 0).Value = Worksheets("Temp").Cells(12, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 0).Value = Worksheets("Temp").Cells(13, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 0).Value = Worksheets("Temp").Cells(14, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 0).Value = Worksheets("Temp").Cells(15, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 0).Value = Worksheets("Temp").Cells(16, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 0).Value = Worksheets("Temp").Cells(17, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 0).Value = Worksheets("Temp").Cells(18, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 0).Value = Worksheets("Temp").Cells(19, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 0).Value = Worksheets("Temp").Cells(20, 6).Value

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(0, 1).Value = Worksheets("Temp").Cells(10, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 1).Value = Worksheets("Temp").Cells(11, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 1).Value = Worksheets("Temp").Cells(12, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 1).Value = Worksheets("Temp").Cells(13, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 1).Value = Worksheets("Temp").Cells(14, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 1).Value = Worksheets("Temp").Cells(15, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 1).Value = Worksheets("Temp").Cells(16, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 1).Value = Worksheets("Temp").Cells(17, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 1).Value = Worksheets("Temp").Cells(18, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 1).Value = Worksheets("Temp").Cells(19, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 1).Value = Worksheets("Temp").Cells(20, 7).Value



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


PART 2 Below


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=539306

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default Can anyone help me tidy up?
Im pretty new at programming for excel too. I started with being amased that
I could have excel add two cells together. Ive been working on a sub that
will combine several worksheets together everytime I need to update and it
seriously cuts my work time in half. Im currently working on a custom
sorting sub thats gotten rather large and complicated. Anyway, I am still
new and I cant really help you cleaning up the code.

But one thing I do to help troubleshoot code (which makes it a lot easier
to update the code too) is throw all the code that outputs into a different
sub that is called by the main and have a Global variable (boolean) that when
I turn it on will have all the outputting subs Exit before any changes are
made and possably put some of the data into a msgbox so you can make sure
everything is running properly.

Another thing that is useful is to have a variable that increments one
number all over the program. If I run into a problem I can tell VBA to break
when that variable changes values and hit F5 until I reach the code that Im
having problems on and F8 through that section to see exactly what the
program is doing.

Those methods and a few others work for me. It may be worth a shot to try
them out if updating and troubleshooting your program is a big concern. (If
you want I can post a small example of what Im talking about if what I said
makes little sense)

"drucey" wrote:


Don't laugh! I've spent weeks doing this (my first proper macro!) and am
so pleased with it!

But, it's rather large.

Any tidying up possible?

Thankyou!


Code:
--------------------
Sub Pick()

'Open a CAP file
ChDrive "J"
ChDir "J:\Accounts\Cap\"
fname = Application.GetOpenFilename()
Workbooks.OpenText Filename:=fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) _
, TrailingMinusNumbers:=True

'Remember the CAP name
Dim CapName As Variant
CapName = ActiveWorkbook.Name



'Find the beginning of useful data
Cells.Find(What:="end of day report for day", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Get rid of all the junk data
Dim L As Long
L = Selection.Row - 3
If L 0 Then
s = "1:" & L
Rows(s).Select
Selection.Delete Shift:=xlUp
End If

'Add appropriate column seperators
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(23, 1), Array(32, 1)), TrailingMinusNumbers _
:=True

'Select all and copy to Workbook
Cells.Select
Selection.Copy
Windows("Stores.xls").Activate
Sheets("Temp").Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows(CapName).Close
Application.DisplayAlerts = True

'Extract the date
Range("F1").Select
ActiveCell.Formula = "=MID(A1,5,11)"
Range("F1").Select
Dim CapDate As Date
CapDate = Range("F1").Value

'Extract the Till number
Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"
Range("F2").Select
Dim TillNumber As Integer
TillNumber = Range("F2").Value

'Find the Time & Value data
Cells.Find(What:="timeband report", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim rng As Range

'Copy the Time & Value data
Set rng = ActiveCell.Offset(4, 1).Resize(24, 2)
rng.Copy

'In main workbook, find which column to paste data into
Windows("Stores.xls").Activate
Sheets("Till0" & TillNumber).Select
Range("A1").Select

'Set the first Zero row
Cells.Find(What:="0000", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Zero As Variant
Zero = ActiveCell.Row

'Set the first payment method row
Cells.Find(What:="Account Sales", LookIn:=xlValues, SearchFormat:=False).Activate
Dim AccSales As Variant
AccSales = ActiveCell.Row

'Set the first discount row
Cells.Find(What:="item discount", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Discounts As Variant
Discounts = ActiveCell.Row

'Search for the Cap Date
Cells.Find(What:=CapDate, LookIn:=xlFormulas, SearchFormat:=False).Activate
Dim CapDateCol As Variant
CapDateCol = ActiveCell.Column

'Paste the Till value and data
Worksheets("Till0" & TillNumber).Cells(Zero, CapDateCol).Select
ActiveSheet.Paste

'Lookup Payment Method data
Worksheets("Temp").Activate
Range("F10").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(1, 0).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(2, 0).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALS E))"
ActiveCell.Offset(3, 0).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(4, 0).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(5, 0).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,2,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,2, FALSE))"
ActiveCell.Offset(6, 0).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(7, 0).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(8, 0).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(9, 0).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(10, 0).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FA LSE))"
ActiveCell.Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT SALES"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT SALES"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(1, 1).Formula = "=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(2, 1).Formula = "=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALS E))"
ActiveCell.Offset(3, 1).Formula = "=IF(ISNA(VLOOKUP(""CREDIT NOTE"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" CREDIT NOTE"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(4, 1).Formula = "=IF(ISNA(VLOOKUP(""DELTA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" DELTA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(5, 1).Formula = "=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,3,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,3, FALSE))"
ActiveCell.Offset(6, 1).Formula = "=IF(ISNA(VLOOKUP(""MASTER CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" MASTER CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(7, 1).Formula = "=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(8, 1).Formula = "=IF(ISNA(VLOOKUP(""SWITCH CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" SWITCH CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(9, 1).Formula = "=IF(ISNA(VLOOKUP(""VISA CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" VISA CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(10, 1).Formula = "=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FA LSE))"


'Copy Payment Method data to summary

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Value = Worksheets("Temp").Cells(10, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 0).Value = Worksheets("Temp").Cells(11, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 0).Value = Worksheets("Temp").Cells(12, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 0).Value = Worksheets("Temp").Cells(13, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 0).Value = Worksheets("Temp").Cells(14, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 0).Value = Worksheets("Temp").Cells(15, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 0).Value = Worksheets("Temp").Cells(16, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 0).Value = Worksheets("Temp").Cells(17, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 0).Value = Worksheets("Temp").Cells(18, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 0).Value = Worksheets("Temp").Cells(19, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 0).Value = Worksheets("Temp").Cells(20, 6).Value

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(0, 1).Value = Worksheets("Temp").Cells(10, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1, 1).Value = Worksheets("Temp").Cells(11, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2, 1).Value = Worksheets("Temp").Cells(12, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3, 1).Value = Worksheets("Temp").Cells(13, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4, 1).Value = Worksheets("Temp").Cells(14, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5, 1).Value = Worksheets("Temp").Cells(15, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6, 1).Value = Worksheets("Temp").Cells(16, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7, 1).Value = Worksheets("Temp").Cells(17, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8, 1).Value = Worksheets("Temp").Cells(18, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9, 1).Value = Worksheets("Temp").Cells(19, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10, 1).Value = Worksheets("Temp").Cells(20, 7).Value



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


PART 2 Below


--
drucey
------------------------------------------------------------------------
drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=539306


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 770
Default Can anyone help me tidy up?
drucey,

One of the key pieces of advice you'll see in this newsgroup is to eliminate
"Select"s and "Activate"s wherever possible - and it's almost always
possible. This speeds up your code a lot. For example, change:

Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"

to:

Range("F2").Formula = "=MID(C3,4,1)"

hth,

Doug

"drucey" wrote in
message ...

Don't laugh! I've spent weeks doing this (my first proper macro!) and am
so pleased with it!

But, it's rather large.

Any tidying up possible?

Thankyou!


Code:
--------------------
Sub Pick()

'Open a CAP file
ChDrive "J"
ChDir "J:\Accounts\Cap\"
fname = Application.GetOpenFilename()
Workbooks.OpenText Filename:=fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
_
, TrailingMinusNumbers:=True

'Remember the CAP name
Dim CapName As Variant
CapName = ActiveWorkbook.Name



'Find the beginning of useful data
Cells.Find(What:="end of day report for day", After:=ActiveCell, LookIn:=
_
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Get rid of all the junk data
Dim L As Long
L = Selection.Row - 3
If L 0 Then
s = "1:" & L
Rows(s).Select
Selection.Delete Shift:=xlUp
End If

'Add appropriate column seperators
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth,
_
FieldInfo:=Array(Array(0, 1), Array(23, 1), Array(32, 1)),
TrailingMinusNumbers _
:=True

'Select all and copy to Workbook
Cells.Select
Selection.Copy
Windows("Stores.xls").Activate
Sheets("Temp").Select
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows(CapName).Close
Application.DisplayAlerts = True

'Extract the date
Range("F1").Select
ActiveCell.Formula = "=MID(A1,5,11)"
Range("F1").Select
Dim CapDate As Date
CapDate = Range("F1").Value

'Extract the Till number
Range("F2").Select
ActiveCell.Formula = "=MID(C3,4,1)"
Range("F2").Select
Dim TillNumber As Integer
TillNumber = Range("F2").Value

'Find the Time & Value data
Cells.Find(What:="timeband report", After:=ActiveCell, LookIn:=xlFormulas
_
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim rng As Range

'Copy the Time & Value data
Set rng = ActiveCell.Offset(4, 1).Resize(24, 2)
rng.Copy

'In main workbook, find which column to paste data into
Windows("Stores.xls").Activate
Sheets("Till0" & TillNumber).Select
Range("A1").Select

'Set the first Zero row
Cells.Find(What:="0000", LookIn:=xlValues, SearchFormat:=False).Activate
Dim Zero As Variant
Zero = ActiveCell.Row

'Set the first payment method row
Cells.Find(What:="Account Sales", LookIn:=xlValues,
SearchFormat:=False).Activate
Dim AccSales As Variant
AccSales = ActiveCell.Row

'Set the first discount row
Cells.Find(What:="item discount", LookIn:=xlValues,
SearchFormat:=False).Activate
Dim Discounts As Variant
Discounts = ActiveCell.Row

'Search for the Cap Date
Cells.Find(What:=CapDate, LookIn:=xlFormulas,
SearchFormat:=False).Activate
Dim CapDateCol As Variant
CapDateCol = ActiveCell.Column

'Paste the Till value and data
Worksheets("Till0" & TillNumber).Cells(Zero, CapDateCol).Select
ActiveSheet.Paste

'Lookup Payment Method data
Worksheets("Temp").Activate
Range("F10").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT
SALES"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT
SALES"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(1, 0).Formula =
"=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(2, 0).Formula =
"=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,2,FALS E))"
ActiveCell.Offset(3, 0).Formula = "=IF(ISNA(VLOOKUP(""CREDIT
NOTE"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" CREDIT
NOTE"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(4, 0).Formula = "=IF(ISNA(VLOOKUP(""DELTA
CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" DELTA
CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(5, 0).Formula =
"=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,2,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,2, FALSE))"
ActiveCell.Offset(6, 0).Formula = "=IF(ISNA(VLOOKUP(""MASTER
CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" MASTER
CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(7, 0).Formula =
"=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(8, 0).Formula = "=IF(ISNA(VLOOKUP(""SWITCH
CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" SWITCH
CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(9, 0).Formula = "=IF(ISNA(VLOOKUP(""VISA
CARD"",Temp!A10:C20,2,FALSE)=TRUE),"""",VLOOKUP("" VISA
CARD"",Temp!A10:C20,2,FALSE))"
ActiveCell.Offset(10, 0).Formula =
"=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,2,FA LSE))"
ActiveCell.Offset(0, 1).Formula = "=IF(ISNA(VLOOKUP(""ACCOUNT
SALES"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP(" "ACCOUNT
SALES"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(1, 1).Formula =
"=IF(ISNA(VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""CASH"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(2, 1).Formula =
"=IF(ISNA(VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALSE) =TRUE),"""",VLOOKUP(""CHEQUE"",Temp!A10:C20,3,FALS E))"
ActiveCell.Offset(3, 1).Formula = "=IF(ISNA(VLOOKUP(""CREDIT
NOTE"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" CREDIT
NOTE"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(4, 1).Formula = "=IF(ISNA(VLOOKUP(""DELTA
CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" DELTA
CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(5, 1).Formula =
"=IF(ISNA(VLOOKUP(""ELECTRON"",Temp!A10:C20,3,FALS E)=TRUE),"""",VLOOKUP(""ELECTRON"",Temp!A10:C20,3, FALSE))"
ActiveCell.Offset(6, 1).Formula = "=IF(ISNA(VLOOKUP(""MASTER
CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" MASTER
CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(7, 1).Formula =
"=IF(ISNA(VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE)=T RUE),"""",VLOOKUP(""SOLO"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(8, 1).Formula = "=IF(ISNA(VLOOKUP(""SWITCH
CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" SWITCH
CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(9, 1).Formula = "=IF(ISNA(VLOOKUP(""VISA
CARD"",Temp!A10:C20,3,FALSE)=TRUE),"""",VLOOKUP("" VISA
CARD"",Temp!A10:C20,3,FALSE))"
ActiveCell.Offset(10, 1).Formula =
"=IF(ISNA(VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FALSE )=TRUE),"""",VLOOKUP(""VOUCHER"",Temp!A10:C20,3,FA LSE))"


'Copy Payment Method data to summary

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Value =
Worksheets("Temp").Cells(10, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1,
0).Value = Worksheets("Temp").Cells(11, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2,
0).Value = Worksheets("Temp").Cells(12, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3,
0).Value = Worksheets("Temp").Cells(13, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4,
0).Value = Worksheets("Temp").Cells(14, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5,
0).Value = Worksheets("Temp").Cells(15, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6,
0).Value = Worksheets("Temp").Cells(16, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7,
0).Value = Worksheets("Temp").Cells(17, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8,
0).Value = Worksheets("Temp").Cells(18, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9,
0).Value = Worksheets("Temp").Cells(19, 6).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10,
0).Value = Worksheets("Temp").Cells(20, 6).Value

Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(0,
1).Value = Worksheets("Temp").Cells(10, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(1,
1).Value = Worksheets("Temp").Cells(11, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(2,
1).Value = Worksheets("Temp").Cells(12, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(3,
1).Value = Worksheets("Temp").Cells(13, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(4,
1).Value = Worksheets("Temp").Cells(14, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(5,
1).Value = Worksheets("Temp").Cells(15, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(6,
1).Value = Worksheets("Temp").Cells(16, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(7,
1).Value = Worksheets("Temp").Cells(17, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(8,
1).Value = Worksheets("Temp").Cells(18, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(9,
1).Value = Worksheets("Temp").Cells(19, 7).Value
Worksheets("Till0" & TillNumber).Cells(AccSales, CapDateCol).Offset(10,
1).Value = Worksheets("Temp").Cells(20, 7).Value



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


PART 2 Below


--
drucey
------------------------------------------------------------------------
drucey's Profile:
http://www.excelforum.com/member.php...o&userid=32553
View this thread: http://www.excelforum.com/showthread...hreadid=539306



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tidy lookup PBcorn Excel Worksheet Functions 2 June 18th 08 08:05 AM
Can anyone help me tidy up? drucey[_33_] Excel Programming 0 May 5th 06 04:47 PM
Tidy Up Pete Excel Discussion (Misc queries) 4 May 9th 05 05:09 PM
Tidy up data Al Mackay Excel Programming 3 April 27th 04 02:16 PM
Tidy up macro! Gareth[_3_] Excel Programming 3 September 18th 03 06:48 PM


All times are GMT +1. The time now is 05:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement