Filling in a dashboard spreadsheet based on a staffing log spreadsheet

up vote
-2
down vote

favorite

This is my VBA code. It's actually adding COUNTIF value to the next sheet of columns. When I'm running this it will take at least 7 minutes. How to decrease that time?

Dim j As Integer

Dim k As Integer





Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn



       sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i





End Sub

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

migrated from stackoverflow.com Aug 20 at 7:26

This question came from our site for professional and enthusiast programmers.

1

After inserting the formula, change it to a value using Cells(23, j)=Cells(23, j).Value . Another option would be changing the Calculation to Manual, insert all formulas, and then calculate all.automateexcel.com/vba/on-off-calculations
– Foxfire And Burns And Burns
Aug 14 at 10:29

2

For i = 3 To lastrow seems useless.
– PaichengWu
Aug 14 at 10:32

1

Why do you need to select the sheet 3 times?
– Mast
Aug 14 at 10:39

3

Can you explain why you wrote what you currently got? How does it solve your problem? Your code appears to be doing odd things that don't make much sense without explanation.
– Mast
Aug 14 at 10:40

I think you diable/enable Formular: Application.Calculation = xlCalculationManual <your loop> Application.Calculation = xlCalculationAutomatic
– VinhCC
Aug 14 at 11:44

add a comment |

up vote
-2
down vote

favorite

This is my VBA code. It's actually adding COUNTIF value to the next sheet of columns. When I'm running this it will take at least 7 minutes. How to decrease that time?

Dim j As Integer

Dim k As Integer





Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn



       sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i





End Sub

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

migrated from stackoverflow.com Aug 20 at 7:26

This question came from our site for professional and enthusiast programmers.

1

After inserting the formula, change it to a value using Cells(23, j)=Cells(23, j).Value . Another option would be changing the Calculation to Manual, insert all formulas, and then calculate all.automateexcel.com/vba/on-off-calculations
– Foxfire And Burns And Burns
Aug 14 at 10:29

2

For i = 3 To lastrow seems useless.
– PaichengWu
Aug 14 at 10:32

1

Why do you need to select the sheet 3 times?
– Mast
Aug 14 at 10:39

3

Can you explain why you wrote what you currently got? How does it solve your problem? Your code appears to be doing odd things that don't make much sense without explanation.
– Mast
Aug 14 at 10:40

I think you diable/enable Formular: Application.Calculation = xlCalculationManual <your loop> Application.Calculation = xlCalculationAutomatic
– VinhCC
Aug 14 at 11:44

add a comment |

up vote
-2
down vote

favorite

This is my VBA code. It's actually adding COUNTIF value to the next sheet of columns. When I'm running this it will take at least 7 minutes. How to decrease that time?

Dim j As Integer

Dim k As Integer





Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn



       sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i





End Sub

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

This is my VBA code. It's actually adding COUNTIF value to the next sheet of columns. When I'm running this it will take at least 7 minutes. How to decrease that time?

Dim j As Integer

Dim k As Integer





Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn



       sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i





End Sub

vba excel time-limit-exceeded

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

edited 1 hour ago

200_success

127k15149412

edited 1 hour ago

200_success

127k15149412

edited 1 hour ago

200_success

127k15149412

asked Aug 14 at 10:19

srivardhan kandike

asked Aug 14 at 10:19

srivardhan kandike

asked Aug 14 at 10:19

srivardhan kandike

migrated from stackoverflow.com Aug 20 at 7:26

This question came from our site for professional and enthusiast programmers.

migrated from stackoverflow.com Aug 20 at 7:26

This question came from our site for professional and enthusiast programmers.

1

After inserting the formula, change it to a value using Cells(23, j)=Cells(23, j).Value . Another option would be changing the Calculation to Manual, insert all formulas, and then calculate all.automateexcel.com/vba/on-off-calculations
– Foxfire And Burns And Burns
Aug 14 at 10:29

2

For i = 3 To lastrow seems useless.
– PaichengWu
Aug 14 at 10:32

1

Why do you need to select the sheet 3 times?
– Mast
Aug 14 at 10:39

3

Can you explain why you wrote what you currently got? How does it solve your problem? Your code appears to be doing odd things that don't make much sense without explanation.
– Mast
Aug 14 at 10:40

I think you diable/enable Formular: Application.Calculation = xlCalculationManual <your loop> Application.Calculation = xlCalculationAutomatic
– VinhCC
Aug 14 at 11:44

add a comment |

1

After inserting the formula, change it to a value using Cells(23, j)=Cells(23, j).Value . Another option would be changing the Calculation to Manual, insert all formulas, and then calculate all.automateexcel.com/vba/on-off-calculations
– Foxfire And Burns And Burns
Aug 14 at 10:29

2

For i = 3 To lastrow seems useless.
– PaichengWu
Aug 14 at 10:32

1

Why do you need to select the sheet 3 times?
– Mast
Aug 14 at 10:39

3

Can you explain why you wrote what you currently got? How does it solve your problem? Your code appears to be doing odd things that don't make much sense without explanation.
– Mast
Aug 14 at 10:40

I think you diable/enable Formular: Application.Calculation = xlCalculationManual <your loop> Application.Calculation = xlCalculationAutomatic
– VinhCC
Aug 14 at 11:44

After inserting the formula, change it to a value using Cells(23, j)=Cells(23, j).Value . Another option would be changing the Calculation to Manual, insert all formulas, and then calculate all.automateexcel.com/vba/on-off-calculations
– Foxfire And Burns And Burns
Aug 14 at 10:29

For i = 3 To lastrow seems useless.
– PaichengWu
Aug 14 at 10:32

Why do you need to select the sheet 3 times?
– Mast
Aug 14 at 10:39

Can you explain why you wrote what you currently got? How does it solve your problem? Your code appears to be doing odd things that don't make much sense without explanation.
– Mast
Aug 14 at 10:40

I think you diable/enable Formular: Application.Calculation = xlCalculationManual <your loop> Application.Calculation = xlCalculationAutomatic
– VinhCC
Aug 14 at 11:44

add a comment |

1 Answer
1

active

oldest

votes

up vote
-3
down vote

I modify the code, I think it is better:

Dim j As Integer

Dim k As Integer







'Disable



Application.Calculation = xlCalculationManual



Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn

    'Comment

       'sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i



'Enable

Application.Calculation = xlCalculationAutomatic





End Sub

answered Aug 14 at 11:47

VinhCC

2

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f202024%2ffilling-in-a-dashboard-spreadsheet-based-on-a-staffing-log-spreadsheet%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
-3
down vote

I modify the code, I think it is better:

Dim j As Integer

Dim k As Integer







'Disable



Application.Calculation = xlCalculationManual



Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn

    'Comment

       'sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i



'Enable

Application.Calculation = xlCalculationAutomatic





End Sub

answered Aug 14 at 11:47

VinhCC

2

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

add a comment |

up vote
-3
down vote

I modify the code, I think it is better:

Dim j As Integer

Dim k As Integer







'Disable



Application.Calculation = xlCalculationManual



Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn

    'Comment

       'sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i



'Enable

Application.Calculation = xlCalculationAutomatic





End Sub

answered Aug 14 at 11:47

VinhCC

2

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

add a comment |

up vote
-3
down vote

I modify the code, I think it is better:

Dim j As Integer

Dim k As Integer







'Disable



Application.Calculation = xlCalculationManual



Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn

    'Comment

       'sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i



'Enable

Application.Calculation = xlCalculationAutomatic





End Sub

answered Aug 14 at 11:47

VinhCC

I modify the code, I think it is better:

Dim j As Integer

Dim k As Integer







'Disable



Application.Calculation = xlCalculationManual



Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False



sheets("Staffing Log").Select

lastrow = Range("F" & Rows.Count).End(xlUp).Row

sheets("Ed Dashboard").Select

lastcolumn = Cells(22, Columns.Count).End(xlToLeft).Column - 5



sheets("Ed Dashboard").Select



For i = 3 To lastrow



   For j = 5 To lastcolumn

    'Comment

       'sheets("Ed Dashboard").Select



          Cells(23, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-1]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R23C4)"

          Cells(24, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-2]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R24C4)"

          Cells(25, j).Value = "=COUNTIFS('Staffing Log'!R2C6:R1048576C6,'Ed Dashboard'!R[-3]C,'Staffing Log'!R2C10:R1048576C10,'Ed Dashboard'!R25C4)"





   Next j



Next i



'Enable

Application.Calculation = xlCalculationAutomatic





End Sub

answered Aug 14 at 11:47

VinhCC

answered Aug 14 at 11:47

VinhCC

answered Aug 14 at 11:47

VinhCC

answered Aug 14 at 11:47

VinhCC

2

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

add a comment |

2

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

You have presented an alternative solution, but haven't reviewed the code. Please edit to show what aspects of the question code prompted you to write this version, and in what ways it's an improvement over the original. It may be worth (re-)reading How to Answer.
– Toby Speight
4 hours ago

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Code Review Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

Use MathJax to format equations. MathJax reference.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk