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:
Div
Department
Basic Life
Voluntary Life
Voluntary Spouse Life
Voluntary Child Life
Long Term Disability
Basic AD&D
Short Term Disability
Total
1
ACT
Admin
$-
$-
$-
$-
$-
$-
$-
$-
2
ACT
AWG
$-
$-
$-
$-
$-
$-
$-
$-
3
ACT
Client Services
$-
$-
$-
$-
$-
$-
$-
$-
4
ACT
DVS
$-
$-
$-
$-
$-
$-
$-
$-
5
ACT
Executive Management
$-
$-
$-
$-
$-
$-
$-
$-
6
ACT
Finance
$-
$-
$-
$-
$-
$-
$-
$-
7
ACT
Finance Payroll
$-
$-
$-
$-
$-
$-
$-
$-
8
ACT
Frontline
$-
$-
$-
$-
$-
$-
$-
$-
9
ACT
HR
$-
$-
$-
$-
$-
$-
$-
$-
10
ACT
Non ED IQ
$-
$-
$-
$-
$-
$-
$-
$-
11
ACT
IT
$-
$-
$-
$-
$-
$-
$-
$-
12
ACT
Legal
$-
$-
$-
$-
$-
$-
$-
$-
13
ACT
Management
$-
$-
$-
$-
$-
$-
$-
$-
14
ACT
Marketing
$-
$-
$-
$-
$-
$-
$-
$-
15
ACT
Ops Admin
$-
$-
$-
$-
$-
$-
$-
$-
16
ACT
PMO
$-
$-
$-
$-
$-
$-
$-
$-
17
ACT
Procurement
$-
$-
$-
$-
$-
$-
$-
$-
18
ACT
QA
$-
$-
$-
$-
$-
$-
$-
$-
19
ACT
Rehab
$-
$-
$-
$-
$-
$-
$-
$-
20
ACT
Rehab Management
$-
$-
$-
$-
$-
$-
$-
$-
21
ACT
Sales & Marketing
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
22
ACTH
Admin
$-
$-
$-
$-
$-
$-
$-
$-
23
ACTH
Executive Management
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
24
CHR
Bad Debt
$-
$-
$-
$-
$-
$-
$-
$-
25
CHR
Executive 5010
$-
$-
$-
$-
$-
$-
$-
$-
26
CHR
Facility 5114
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-

<tbody>
</tbody>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
Div
Department
Basic Life
Voluntary Life
Voluntary Spouse Life
Voluntary Child Life
Long Term Disability
Basic AD&D
Short Term Disability
Total
1
ACT
Admin
$-
$-
$-
$-
$-
$-
$-
$-
2
ACT
AWG
$-
$-
$-
$-
$-
$-
$-
$-
3
ACT
Client Services
$-
$-
$-
$-
$-
$-
$-
$-
4
ACT
DVS
$-
$-
$-
$-
$-
$-
$-
$-
5
ACT
Executive Management
$-
$-
$-
$-
$-
$-
$-
$-
6
ACT
Finance
$-
$-
$-
$-
$-
$-
$-
$-
7
ACT
Finance Payroll
$-
$-
$-
$-
$-
$-
$-
$-
8
ACT
Frontline
$-
$-
$-
$-
$-
$-
$-
$-
9
ACT
HR
$-
$-
$-
$-
$-
$-
$-
$-
10
ACT
Non ED IQ
$-
$-
$-
$-
$-
$-
$-
$-
11
ACT
IT
$-
$-
$-
$-
$-
$-
$-
$-
12
ACT
Legal
$-
$-
$-
$-
$-
$-
$-
$-
13
ACT
Management
$-
$-
$-
$-
$-
$-
$-
$-
14
ACT
Marketing
$-
$-
$-
$-
$-
$-
$-
$-
15
ACT
Ops Admin
$-
$-
$-
$-
$-
$-
$-
$-
16
ACT
PMO
$-
$-
$-
$-
$-
$-
$-
$-
17
ACT
Procurement
$-
$-
$-
$-
$-
$-
$-
$-
18
ACT
QA
$-
$-
$-
$-
$-
$-
$-
$-
19
ACT
Rehab
$-
$-
$-
$-
$-
$-
$-
$-
20
ACT
Rehab Management
$-
$-
$-
$-
$-
$-
$-
$-
21
ACT
Sales & Marketing
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
22
ACTH
Admin
$-
$-
$-
$-
$-
$-
$-
$-
23
ACTH
Executive Management
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
24
CHR
Bad Debt
$-
$-
$-
$-
$-
$-
$-
$-
25
CHR
Executive 5010
$-
$-
$-
$-
$-
$-
$-
$-
26
CHR
Facility 5114
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-
$-

<tbody>
</tbody>
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

P
  • Question
Help with Vba Code
Replies
3
Views
157
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
954
Joe4
Joe4
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
314
kevin9999
K
D
  • Question
VBA optimization advice
Replies
3
Views
93
bobsan42
bobsan42
G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
362
rlv01
R

Forum statistics

Threads
1,214,826
Messages
6,121,795
Members
449,048
Latest member
greyangel23

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