Public Enum eg_Columns
colCustPO = 1
colXmitID ' 2 B
colCustName ' 3 C
colAcctNum ' 4 D
colGroupWhse ' 5 E
colInvcNum ' 6 F
colInvcLn ' 7 G
colOrderNum ' 8 H
colOrderLn ' 9 I
colTPMNum ' 10 J
colTPMLn ' 11 K
col1stTry ' 12 L
colAllocD ' 13 M
colRecdD ' 14 N
colEntryD ' 15 O
colPODate = 15 ' (column redefinition)
colPOTAT ' 16 P
'...
colMBoL ' 51 AY
colPNSupp ' 52 AZ
colPartDesc ' 53 BA
MaxCount = 53
End Enum
'...
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Private Sub TrimAndWriteFormulas()
'______________________________________________________________________________
Dim rngCurr As Range, rngCurr2 As Range, rngTarget As Range, rngTCell As Range
Dim v As Variant
Dim r&, c%, lngCalcMode As XlCalculation
Set rngCurr = [A1].CurrentRegion
Set rngCurr2 = rngCurr.Offset(1, 0).Resize(rngCurr.Rows.Count - 1)
ufxl_ProgressIndicator.SubLabelCaption = "Trimming Spaces"
ufxl_ProgressIndicator.UpdateProgressMajor 0.001, "TrimSpaces"
myTrim rngCurr2.Columns(eg_Columns.colCustName), " [1 of 10]"
ufxl_ProgressIndicator.UpdateProgressMajor 0.1, "TrimSpaces"
'...
Set rngTarget = rngCurr2.Columns(eg_Columns.colBatchNDay) ' batched by next bus day
' =NETWORKDAYS(RC[-3],RC[1], Holidays) <= 2
Application.StatusBar = "Writing Formula for 'Batched by Next Bus. Day'."
rngTarget.FormulaR1C1 = "=NETWORKDAYS(RC[" & _
(eg_Columns.colOpenD - eg_Columns.colBatchNDay) & _
"],RC[" & _
(eg_Columns.colOpenD - eg_Columns.colBatchNDay) & _
"], Holidays) <= 2"
ufxl_ProgressIndicator.UpdateProgressMajor 4 / 15, "WriteFormulae"
PinteLo rngTarget
Set rngTarget = rngCurr2.Columns(eg_Columns.colTimePick) ' time to pick
Application.StatusBar = "Writing Formula for 'Time to Pick'."
' =(+RC[-2]+RC[-1])-(RC[-6]+RC[-5])
rngTarget.FormulaR1C1 = "=(+RC[" & _
(eg_Columns.colPickD - eg_Columns.colTimePick) & _
"]+RC[" & _
(eg_Columns.colPickT - eg_Columns.colTimePick) & _
"])-(RC[" & _
(eg_Columns.colBatchD - eg_Columns.colTimePick) & _
"]+RC[" & _
(eg_Columns.colBatchT - eg_Columns.colTimePick) & _
"])"
ufxl_ProgressIndicator.UpdateProgressMajor 5 / 15, "WriteFormulae"
PinteLo rngTarget, 1
'...
End Sub