I need help with copy/paste function in Excel Macro VBA and SUMIFS! Keep drawing blanks!

T

tommybone30

New Member
Joined
Dec 14, 2017
Messages
14
Hello, I really need some help!

Keep in mind that I use the Record Macro function liberally, so this may have to do with my limited knowledge of actual coding procedures. But here goes my explanation:

In Macro Excel VBA, I've coded into VBA the formulas of SUMIFS functions for each column listed below ("Basic Life", "Voluntary Life", "Voluntary Spouse Life", etc.); the Total column is computed using the SUM formula. The SUMIFS formula is written as:

=SUMIFS('Basic Life'!L:L, 'Basic Life'!F:F,Match!B70,'Basic Life'!I:I,Match!C70)*2

The other columns are essentially written with the similar formula, just with a different worksheet name (e.g. instead of 'Basic Life', it's 'Voluntary Life', and the next column 'Voluntary Spouse Life', etc.).

I then coded the copy and paste formulas to have it computed to the appropriate rows (as can be seen below).

However, when the Macro computed the code, while everything else seems fine, the formula I've written for each cell that contains the "$ -" do not automatically compute to what's in the other worksheets. I've been trying to get it to work, but it always seems to draw blank, even though there are data in the other worksheets. Any ideas what I may be doing wrong?

I hope the visual aids below will give you better insight into my coding problems.

Here's the code:

Range("D5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Basic Life'!C[8], 'Basic Life'!C[2],Match!RC[-2],'Basic Life'!C[5],Match!RC[-1])*2"
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary Life'!C[7],'Voluntary Life'!C[1],Match!RC[-3],'Voluntary Life'!C[4],Match!RC[-2])*2"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary SP Life'!C[6],'Voluntary SP Life'!C,Match!RC[-4],'Voluntary SP Life'!C[3],Match!RC[-3])*2"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary CH Life'!C[4],'Voluntary CH Life'!C[-1],Match!RC[-5],'Voluntary CH Life'!C[2],Match!RC[-4])*2"
Range("H5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(LTD!C[4],LTD!C[-2],Match!RC[-6],LTD!C[1],Match!RC[-5])*2"
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Basic AD&D'!C[3],'Basic AD&D'!C[-3],Match!RC[-7],'Basic AD&D'!C,Match!RC[-6])*2"
Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(STD!C[2],STD!C[-4],Match!RC[-8],STD!C[-1],Match!RC[-7])*2"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7]:RC[-1])"
Range("D5:K5").Select
Selection.Copy
Range("D5:K25").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("M13").Select
Application.CutCopyMode = False
Range("D25:K25").Select
Selection.Copy
Range("D28:K29").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D32:K34").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D37:K56").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D59:K66").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D69:K97").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


And here's the computed Excel based on that code:
[TABLE="width: 630"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Div
[/TD]
[TD="class: xl70, width: 200, bgcolor: transparent"]Department
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Basic Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Spouse Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Child Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Long Term Disability
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Basic AD&D
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Short Term Disability
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Total
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]AWG
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Client Services
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]DVS
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Finance
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Finance Payroll
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Frontline
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]HR
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Non ED IQ
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]IT
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Legal
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Marketing
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Ops Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]PMO
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Procurement
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]QA
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Rehab
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Rehab Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Sales & Marketing
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACTH
[/TD]
[TD="class: xl66, bgcolor: transparent"]Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACTH
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Bad Debt
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive 5010
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Facility 5114
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
tommybone30 said:
Hello, I really need some help!

Keep in mind that I use the Record Macro function liberally, so this may have to do with my limited knowledge of actual coding procedures. But here goes my explanation:

In Macro Excel VBA, I've coded into VBA the formulas of SUMIFS functions for each column listed below ("Basic Life", "Voluntary Life", "Voluntary Spouse Life", etc.); the Total column is computed using the SUM formula. The SUMIFS formula is written as:

=SUMIFS('Basic Life'!L:L, 'Basic Life'!F:F,Match!B70,'Basic Life'!I:I,Match!C70)*2

The other columns are essentially written with the similar formula, just with a different worksheet name (e.g. instead of 'Basic Life', it's 'Voluntary Life', and the next column 'Voluntary Spouse Life', etc.).

I then coded the copy and paste formulas to have it computed to the appropriate rows (as can be seen below).

However, when the Macro computed the code, while everything else seems fine, the formula I've written for each cell that contains the "$ -" do not automatically compute to what's in the other worksheets. I've been trying to get it to work, but it always seems to draw blank, even though there are data in the other worksheets. Any ideas what I may be doing wrong?

I hope the visual aids below will give you better insight into my coding problems.

Here's the code:

Range("D5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Basic Life'!C[8], 'Basic Life'!C[2],Match!RC[-2],'Basic Life'!C[5],Match!RC[-1])*2"
Range("E5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary Life'!C[7],'Voluntary Life'!C[1],Match!RC[-3],'Voluntary Life'!C[4],Match!RC[-2])*2"
Range("F5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary SP Life'!C[6],'Voluntary SP Life'!C,Match!RC[-4],'Voluntary SP Life'!C[3],Match!RC[-3])*2"
Range("G5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Voluntary CH Life'!C[4],'Voluntary CH Life'!C[-1],Match!RC[-5],'Voluntary CH Life'!C[2],Match!RC[-4])*2"
Range("H5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(LTD!C[4],LTD!C[-2],Match!RC[-6],LTD!C[1],Match!RC[-5])*2"
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS('Basic AD&D'!C[3],'Basic AD&D'!C[-3],Match!RC[-7],'Basic AD&D'!C,Match!RC[-6])*2"
Range("J5").Select
ActiveCell.FormulaR1C1 = _
"=SUMIFS(STD!C[2],STD!C[-4],Match!RC[-8],STD!C[-1],Match!RC[-7])*2"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-7]:RC[-1])"
Range("D5:K5").Select
Selection.Copy
Range("D5:K25").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("M13").Select
Application.CutCopyMode = False
Range("D25:K25").Select
Selection.Copy
Range("D28:K29").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D32:K34").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D37:K56").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D59:K66").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("D69:K97").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


And here's the computed Excel based on that code:
[TABLE="width: 630"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Div
[/TD]
[TD="class: xl70, width: 200, bgcolor: transparent"]Department
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Basic Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Spouse Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Voluntary Child Life
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Long Term Disability
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Basic AD&D
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Short Term Disability
[/TD]
[TD="class: xl73, width: 64, bgcolor: transparent"]Total
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]AWG
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Client Services
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]DVS
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Finance
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Finance Payroll
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Frontline
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]HR
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Non ED IQ
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]IT
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Legal
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Marketing
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Ops Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]PMO
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]17
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Procurement
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]QA
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Rehab
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Rehab Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACT
[/TD]
[TD="class: xl66, bgcolor: transparent"]Sales & Marketing
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACTH
[/TD]
[TD="class: xl66, bgcolor: transparent"]Admin
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="class: xl65, bgcolor: transparent"]ACTH
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive Management
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]24
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Bad Debt
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Executive 5010
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl65, bgcolor: transparent"]CHR
[/TD]
[TD="class: xl66, bgcolor: transparent"]Facility 5114
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl71, bgcolor: transparent"]$-
[/TD]
[TD="class: xl72, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[TD="class: xl74, bgcolor: transparent"]$-
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Is the question too vague for people to understand this question?
 
Upvote 0
I do not understand why you need a Vba script to enter formulas in your cells.

A formula in Range("A1") may be =B1
so what ever was in "B1" is now in A1

Or I could write into my Vba script this

Range("A1").Value=Range("B1").value

Not sure why I would want a Vba script to enter formulas into a cell when Vba can just put the results you want in the cell.

Would you just briefly explain what your wanting to do and maybe we could write a script for you. But always provide specific things like sheet names and ranges. And say column A or Column B

Not column Start and column Day
 
Upvote 0
The reason why I'm entering formulas in the cells using VBA is simply because my boss wants to apply automation to each month's Cigna Life Excel data. The goal is to significantly ease the workload for all the other accountants so that they don't have to manually insert the formulas every single month, especially when dealing with a company with thousands of employees across multiple benefits options.

As for the Excel data that I've provided in the post above, Column A is where the descending number orders are located, so anything below the header in Columns D, E, F, G, H, I, and J are the ones that I'm having trouble with having the formula auto-compute (for some reason, Column K, or "Total" column, seems to auto-compute fine).
 
Upvote 0
I'm sorry but this is beyond my Knowledgebase.
Hopefully someone else here at Mr. Excel will be able to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
191
Alex Blakenburg
A
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
474
Sean15
Sean15
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
354
RoryA
RoryA
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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