code cleanup - anyone interested enough to take a look?

Status
Not open for further replies.

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
Office Version
  1. 365
Platform
  1. Windows
I have been making some changes to a petty cash worksheet over the last couple of weeks and have now got it to where its almost ready to be set free in the wilds of our finance department. however, its doing some weird stuff where i thought it should be rather straight forward. it has four sheets, two of which are hidden - the first contains lookup tables, business rules, validation lists, etc and the second one is a clipboard of sorts used to split particular expenses before writing them back to the main spreadsheet.

The main spreadsheet has two buttons on it: one for creating the pivot table Summary of the expenses, and the second to reset sheet level event code and/or clear the contents to begin a new month.

firstly, if there is nothing on the data sheet, and the reset sheet button is pressed, i can get a run time error stating that I can not do this on a protected sheet, but then when i press it again, it runs without hassle.

secondly, while mucking about with the protection function i have, i noticed that everytime i ran the reset macro, my ComboBox change event ran through a couple of tiimes.

the following is in my Data Input sheet:

[face=Calibri]Option Explicit

Private Sub ComboBox1_Change()
SetProtection (False)
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Range("b4").Select
SetProtection (True)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Origin As Excel.Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False

End With

If Sheets("Parameters").Range("MacroOff") Then Exit Sub
If Sheets("Parameters").Range("HasCalc") Then Exit Sub
If ActiveCell.Row < 10 Then Exit Sub
If ActiveCell.Column > 12 Then Exit Sub
Set Origin = Sheets("Data Input").Cells(Sheets("Parameters").Range("ActiveCellRow"), _
Sheets("Parameters").Range("ActiveCellColumn"))
'///sets "Origin" to be the active cell

Select Case Sheets("Parameters").Range("ActiveCellColumn")
'/// "ORIGIN" VALUE (Receipt amount):
Case 5 'Amount
If Origin = 0 Then
Sheets("Parameters").Range("HasCalc") = True
SetProtection (False)
Origin.Offset(0, 1).Formula = ""
Origin.Offset(0, 2).Formula = ""
SetProtection (True)
Sheets("Parameters").Range("HasCalc") = False
Else
Sheets("Parameters").Range("HasCalc") = True
SetProtection (False)
Range("F" & ActiveCell.Row).FormulaR1C1 = "=+ROUND(IF(RC4=""GST"",RC5/11,0),2)"
Range("G" & ActiveCell.Row).FormulaR1C1 = "=+RC[-2]-RC[-1]"
SetProtection (True)
Sheets("Parameters").Range("HasCalc") = False
End If
'/// "ORIGIN" VALUE (GL Expense Account):
Case 8 'Expense Account
Select Case Left(Origin, 1)

'///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 3:
Case "3"
'///if selected unibis branch starts with CH 999, clear this branch
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
'///also, if Activity is other than 2,3,or 4, clear Activity
If Left(Origin.Offset(0, 2), 1) <> "2" And _
Left(Origin.Offset(0, 2), 1) <> "3" And _
Left(Origin.Offset(0, 2), 1) <> "4" Then
Origin.Offset(0, 2) = ""
End If
'///when FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 8:
Case "8"
'///same as above for Branch
If Left(Origin.Offset(0, 1), 6) = "CH 999" Then
Origin.Offset(0, 1) = ""
End If
'///also, if Activity is other than 5,5,7,or 8, clear Activity
If Left(Origin.Offset(0, 2), 1) <> "5" And _
Left(Origin.Offset(0, 2), 1) <> "6" And _
Left(Origin.Offset(0, 2), 1) <> "7" And _
Left(Origin.Offset(0, 2), 1) <> "8" Then
Origin.Offset(0, 2) = ""
End If
'///IF FIRST CHARACTER OF "ORIGIN" VALUE (GL Exp Acc)IS 0:
Case "0"
If Left(Origin, 1) = "0" Then
'///set activity and set unibis branch
Origin.Offset(0, 2) = "0 - 999"
Origin.Offset(0, 1) = "CH 999 - Balance Sheet"
End If
End Select
'/// "ORIGIN" VALUE (Unibis Branch):
Case 9 'Location
Select Case True
'///when user selects this Unibis branch
Case Origin = "QF QHR - TN TOLL REF- Administration"
'/// if activity is other than 6 or 8, clear
If Left(Origin.Offset(0, 1), 1) <> "6" And _
Left(Origin.Offset(0, 1), 1) <> "8" Then
Origin.Offset(0, 1) = ""
End If
'///when unibis location starts with "CH"
Case Left(Origin, 2) = "CH"
'///set activity to this value
Origin.Offset(0, 1) = "7 - CRP"
End Select
Case 10 'Activity
Select Case True
'///when Activity starts with 0
Case Left(Origin, 1) = "0"
'///if GL account starts with anything but 0, clear GL Account
'///and input CH 999 - BS in Unibis Branch
If Left(Origin.Offset(0, -2), 1) <> "0" Then
Origin.Offset(0, -2) = ""
Origin.Offset(0, -1) = "CH 999 - Balance Sheet"
End If
'///when Activity starts with 2,3,or 4
Case Left(Origin, 1) = "2", _
Left(Origin, 1) = "3", _
Left(Origin, 1) = "4"
'///if GL starts with other than 3, clear GL
If Left(Origin.Offset(0, -2), 1) <> "3" Then
Origin.Offset(0, -2) = ""
End If
'///when Activity starts with 5,6,7, or 8
Case Left(Origin, 1) = "5", _
Left(Origin, 1) = "6", _
Left(Origin, 1) = "7", _
Left(Origin, 1) = "8"
'///if GL starts with other than 8, clear GL
If Left(Origin.Offset(0, -2), 1) <> "8" Then
Origin.Offset(0, -2) = ""
End If
End Select
End Select
Sheets("Parameters").Range("ActiveCellColumn") = Target.Column
If Target.Row > 9 Then Sheets("Parameters").Range("ActiveCellRow") = Target.Row

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

[/face]

while the following is in two modules: the first:[face=Calibri]Option Explicit

Sub CalculatePivot()
If Sheets("Parameters").Range("MacroOff") Then Exit Sub
SetProtection (False)

Application.Run "splittercode"

SetProtection (True)
Sheets("Summary").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

Sub ResetSheet()
Dim Response As Variant

If Sheets("Parameters").Range("MacroOff") Then Exit Sub
SetProtection (False)
Response = MsgBox("Clear all records from sheet?", vbCritical + vbYesNoCancel, "Reset")
Select Case Response
Case vbNo
Sheets("Parameters").Range("HasCalc") = False
Exit Sub
Case vbCancel
Exit Sub
Case vbYes
Range("A10").Select
Selection.CurrentRegion.Select
Range("A10:j" & LTrim(Str(Selection.Row + Selection.Rows.Count - 1))).ClearContents

Range("CashOnHand").ClearContents
Range("InputDate").ClearContents
Range("InputWeek").ClearContents
Range("A10").Select
Sheets("Parameters").Range("HasCalc") = False
SetProtection (True)
MsgBox "Sheet has been reset"
End Select
Calculate
End Sub

Public Sub SetProtection(sProtect As Boolean)
If Sheets("Parameters").Range("MacroOff") Then Exit Sub
Select Case sProtect
Case True
If Sheets("Parameters").Range("Protection") Then
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
Case False
ActiveSheet.Unprotect
End Select
End Sub
[/face]
and the second (which deals with expenses to be split)[face=Calibri]Sub splittercode()
'Sub SearchForString()

Dim LSearchRow As Integer
'Dim LCopyToRow As Integer
Dim ToBeSplit As Range
Dim SplitTerm As String
With Application
.ScreenUpdating = False

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 10
SplitTerm = Sheets("Parameters").Range("Spliff").Value

With Sheets("Data Input")
While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = "Mail Box", copy entire row to Sheet2
If Range("I" & CStr(LSearchRow)).Value = SplitTerm Then

'Select row in Data Input to copy
Set ToBeSplit = .Range(Cells(CStr(LSearchRow), 1), Cells(CStr(LSearchRow), 10))
SplitVal = .Range("i" & LSearchRow).Address

ToBeSplit.Copy '///copy the active range

Sheets("SplitSht").Range("SplitOne").PasteSpecial (xlPasteValuesAndNumberFormats) '///paste it to splitter range
Application.CutCopyMode = False

Calculate

.Range(SplitVal).Validation.Delete '///remove validation from column K

With ToBeSplit
.ClearContents '///empty range holding expense to be split
.Copy '///copy this empty range
.Offset(1).EntireRow.Insert shift:=xlDown '///paste empty range down one row
.Offset(1).EntireRow.Insert shift:=xlDown '///paste empty range down another row. result is three empty lines with only
End With

Application.CutCopyMode = False
Sheets("SplitSht").Range("SplitResults").Copy
Sheets("Data Input").Cells(LSearchRow, 1).PasteSpecial (xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False

LSearchRow = LSearchRow + 3

Else
LSearchRow = LSearchRow + 1

End If

Wend
End With

Exit Sub

Err_Execute:
MsgBox "An error occurred."
.ScreenUpdating = True
End With

End Sub
[/face]

and this is what the Data Input tab looks like.
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">BRANCH:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">TREF MORNINGSIDE-ADMIN</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Authorised Signature</td><td style="text-align: center;border-top: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">VENDOR No:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">2PETTY27</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">RECONCILIATION</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">DATE:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-left: 1px solid black;text-decoration: underline;;">V2011.3</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CLAIMED VOUCHERS</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> - </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">WEEK NO:</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TOTALS</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CASH ON HAND (Incl Unclaimed Vouchers)</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Receipt Total</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GST</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Excl. Total</td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">FLOAT</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> 500.00 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;border-right: 1px solid black;text-decoration: underline;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CASH ON HAND RECONCILIATION</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"> 500.00 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Purchase Date</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Purchased By</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Description</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Code</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Invoice /Receipt Amount</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GST</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Tax Excl. Amount</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">GL Expense A/C</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Unibis Branch</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Activity</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;background-color: #CCFFFF;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Data Input</p><br /><br />
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
What a mess - time to start again
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,224,416
Messages
6,178,512
Members
452,853
Latest member
philipnjk64

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top