VBA - Executing a loop within Each Worksheet

up vote
0
down vote

favorite

Thanks in advance for all help; I am very much a beginner and appreciate your time!

I have a VBA script that executes perfectly in the first part (add two columns into each worksheet.

However, the second part of the script (summarizes totals in the new columns, in each worksheet) only executes on the first tab, not the others.

I know that this can be done using SUMIFs instead of VBA, but I am learning VBA now, and have to use that.

The code is below:

Sub test()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)

    Dim ticker_name As String

    Dim ticker_total As Double

    Dim summary_table_Row As Double

    Dim i As Double



    ticker_total = 0

    summary_table_Row = 2



    For Each ws In wb.Worksheets

        'Create columns for ticker totals

        ws.Range("I1").Value = "Ticker"

        ws.Range("J1").Value = "Total Stock Volume"



        'Autofit to display data

        '  ws.Columns("A:J").AutoFit



        'THE SECTION BELOW IS THE ONE THAT WILL NOT LOOP THROUGH EACH TAB.

        '_________________________________________________________________



        'Do While IsEmpty(ws.Cells(i + 1, 1)) = True

        For i = 2 To 99999

            ' Check if we are still within the same credit card brand, if it is not...

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

              ' Set the Brand name

              ticker_name = ws.Cells(i, 1).Value



              ' Add to the Brand Total

              ticker_total = ticker_total + ws.Cells(i, 7).Value



              ' Print the Credit Card Brand in the Summary Table

              ws.Range("I" & summary_table_Row).Value = ticker_name



              ' Print the Brand Amount to the Summary Table

              ws.Range("J" & summary_table_Row).Value = ticker_total



              ' Add one to the summary table row

              summary_table_Row = summary_table_Row + 1



              ' Reset the Brand Total

              ticker_total = 0



            ' If the cell immediately following a row is the same brand...

            Else

              ' Add to the Brand Total

               ticker_total = ticker_total + ws.Cells(i, 7).Value

            End If

        Next i

        'Loop

    Next ws

End Sub

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

3

Is it possible that the data is aggregating, but just below off screen? From reading your code, it seems that each sheet totals information in columns I:J. You don't, however, reset summary_table_row to row 2 for each worksheet. So, for example, if sheet1 takes 200 rows, then sheet2 will start aggregating information in row 201. If my understanding is correct, then after the code Next i, try adding a line for summary_table_row = 2
– user3561813
Nov 19 at 16:50

Thanks for your response. There is similar data in each tab, and the data in each tab should aggregate in columns "J". The problem is that this aggregateation occurs only on the first tab, and does not loop into the other tabs.
– Earl
Nov 19 at 18:16

Did you try what I recommended? You have to reset the summary_table_row variable for each sheet.
– user3561813
Nov 19 at 18:35

summary_table_Row = 2 should be just above For i = 2 To 99999
– Tim Williams
Nov 19 at 18:51

Bingo, Bingo! Thank you so, so much. If you are in the US, please have a happy Thanksgiving!
– Earl
Nov 19 at 19:53

add a comment |

up vote
0
down vote

favorite

Thanks in advance for all help; I am very much a beginner and appreciate your time!

I have a VBA script that executes perfectly in the first part (add two columns into each worksheet.

However, the second part of the script (summarizes totals in the new columns, in each worksheet) only executes on the first tab, not the others.

I know that this can be done using SUMIFs instead of VBA, but I am learning VBA now, and have to use that.

The code is below:

Sub test()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)

    Dim ticker_name As String

    Dim ticker_total As Double

    Dim summary_table_Row As Double

    Dim i As Double



    ticker_total = 0

    summary_table_Row = 2



    For Each ws In wb.Worksheets

        'Create columns for ticker totals

        ws.Range("I1").Value = "Ticker"

        ws.Range("J1").Value = "Total Stock Volume"



        'Autofit to display data

        '  ws.Columns("A:J").AutoFit



        'THE SECTION BELOW IS THE ONE THAT WILL NOT LOOP THROUGH EACH TAB.

        '_________________________________________________________________



        'Do While IsEmpty(ws.Cells(i + 1, 1)) = True

        For i = 2 To 99999

            ' Check if we are still within the same credit card brand, if it is not...

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

              ' Set the Brand name

              ticker_name = ws.Cells(i, 1).Value



              ' Add to the Brand Total

              ticker_total = ticker_total + ws.Cells(i, 7).Value



              ' Print the Credit Card Brand in the Summary Table

              ws.Range("I" & summary_table_Row).Value = ticker_name



              ' Print the Brand Amount to the Summary Table

              ws.Range("J" & summary_table_Row).Value = ticker_total



              ' Add one to the summary table row

              summary_table_Row = summary_table_Row + 1



              ' Reset the Brand Total

              ticker_total = 0



            ' If the cell immediately following a row is the same brand...

            Else

              ' Add to the Brand Total

               ticker_total = ticker_total + ws.Cells(i, 7).Value

            End If

        Next i

        'Loop

    Next ws

End Sub

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

3

Is it possible that the data is aggregating, but just below off screen? From reading your code, it seems that each sheet totals information in columns I:J. You don't, however, reset summary_table_row to row 2 for each worksheet. So, for example, if sheet1 takes 200 rows, then sheet2 will start aggregating information in row 201. If my understanding is correct, then after the code Next i, try adding a line for summary_table_row = 2
– user3561813
Nov 19 at 16:50

Thanks for your response. There is similar data in each tab, and the data in each tab should aggregate in columns "J". The problem is that this aggregateation occurs only on the first tab, and does not loop into the other tabs.
– Earl
Nov 19 at 18:16

Did you try what I recommended? You have to reset the summary_table_row variable for each sheet.
– user3561813
Nov 19 at 18:35

summary_table_Row = 2 should be just above For i = 2 To 99999
– Tim Williams
Nov 19 at 18:51

Bingo, Bingo! Thank you so, so much. If you are in the US, please have a happy Thanksgiving!
– Earl
Nov 19 at 19:53

add a comment |

up vote
0
down vote

favorite

Thanks in advance for all help; I am very much a beginner and appreciate your time!

I have a VBA script that executes perfectly in the first part (add two columns into each worksheet.

However, the second part of the script (summarizes totals in the new columns, in each worksheet) only executes on the first tab, not the others.

I know that this can be done using SUMIFs instead of VBA, but I am learning VBA now, and have to use that.

The code is below:

Sub test()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)

    Dim ticker_name As String

    Dim ticker_total As Double

    Dim summary_table_Row As Double

    Dim i As Double



    ticker_total = 0

    summary_table_Row = 2



    For Each ws In wb.Worksheets

        'Create columns for ticker totals

        ws.Range("I1").Value = "Ticker"

        ws.Range("J1").Value = "Total Stock Volume"



        'Autofit to display data

        '  ws.Columns("A:J").AutoFit



        'THE SECTION BELOW IS THE ONE THAT WILL NOT LOOP THROUGH EACH TAB.

        '_________________________________________________________________



        'Do While IsEmpty(ws.Cells(i + 1, 1)) = True

        For i = 2 To 99999

            ' Check if we are still within the same credit card brand, if it is not...

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

              ' Set the Brand name

              ticker_name = ws.Cells(i, 1).Value



              ' Add to the Brand Total

              ticker_total = ticker_total + ws.Cells(i, 7).Value



              ' Print the Credit Card Brand in the Summary Table

              ws.Range("I" & summary_table_Row).Value = ticker_name



              ' Print the Brand Amount to the Summary Table

              ws.Range("J" & summary_table_Row).Value = ticker_total



              ' Add one to the summary table row

              summary_table_Row = summary_table_Row + 1



              ' Reset the Brand Total

              ticker_total = 0



            ' If the cell immediately following a row is the same brand...

            Else

              ' Add to the Brand Total

               ticker_total = ticker_total + ws.Cells(i, 7).Value

            End If

        Next i

        'Loop

    Next ws

End Sub

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

Thanks in advance for all help; I am very much a beginner and appreciate your time!

I have a VBA script that executes perfectly in the first part (add two columns into each worksheet.

However, the second part of the script (summarizes totals in the new columns, in each worksheet) only executes on the first tab, not the others.

I know that this can be done using SUMIFs instead of VBA, but I am learning VBA now, and have to use that.

The code is below:

Sub test()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets(1)

    Dim ticker_name As String

    Dim ticker_total As Double

    Dim summary_table_Row As Double

    Dim i As Double



    ticker_total = 0

    summary_table_Row = 2



    For Each ws In wb.Worksheets

        'Create columns for ticker totals

        ws.Range("I1").Value = "Ticker"

        ws.Range("J1").Value = "Total Stock Volume"



        'Autofit to display data

        '  ws.Columns("A:J").AutoFit



        'THE SECTION BELOW IS THE ONE THAT WILL NOT LOOP THROUGH EACH TAB.

        '_________________________________________________________________



        'Do While IsEmpty(ws.Cells(i + 1, 1)) = True

        For i = 2 To 99999

            ' Check if we are still within the same credit card brand, if it is not...

            If ws.Cells(i + 1, 1).Value <> ws.Cells(i, 1).Value Then

              ' Set the Brand name

              ticker_name = ws.Cells(i, 1).Value



              ' Add to the Brand Total

              ticker_total = ticker_total + ws.Cells(i, 7).Value



              ' Print the Credit Card Brand in the Summary Table

              ws.Range("I" & summary_table_Row).Value = ticker_name



              ' Print the Brand Amount to the Summary Table

              ws.Range("J" & summary_table_Row).Value = ticker_total



              ' Add one to the summary table row

              summary_table_Row = summary_table_Row + 1



              ' Reset the Brand Total

              ticker_total = 0



            ' If the cell immediately following a row is the same brand...

            Else

              ' Add to the Brand Total

               ticker_total = ticker_total + ws.Cells(i, 7).Value

            End If

        Next i

        'Loop

    Next ws

End Sub

excel vba loops worksheet

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

edited Nov 19 at 16:30

Lee Mac

3,21721338

edited Nov 19 at 16:30

Lee Mac

3,21721338

edited Nov 19 at 16:30

Lee Mac

3,21721338

asked Nov 19 at 16:22

Earl

asked Nov 19 at 16:22

Earl

asked Nov 19 at 16:22

Earl

3

Is it possible that the data is aggregating, but just below off screen? From reading your code, it seems that each sheet totals information in columns I:J. You don't, however, reset summary_table_row to row 2 for each worksheet. So, for example, if sheet1 takes 200 rows, then sheet2 will start aggregating information in row 201. If my understanding is correct, then after the code Next i, try adding a line for summary_table_row = 2
– user3561813
Nov 19 at 16:50

Thanks for your response. There is similar data in each tab, and the data in each tab should aggregate in columns "J". The problem is that this aggregateation occurs only on the first tab, and does not loop into the other tabs.
– Earl
Nov 19 at 18:16

Did you try what I recommended? You have to reset the summary_table_row variable for each sheet.
– user3561813
Nov 19 at 18:35

summary_table_Row = 2 should be just above For i = 2 To 99999
– Tim Williams
Nov 19 at 18:51

Bingo, Bingo! Thank you so, so much. If you are in the US, please have a happy Thanksgiving!
– Earl
Nov 19 at 19:53

add a comment |

3

Is it possible that the data is aggregating, but just below off screen? From reading your code, it seems that each sheet totals information in columns I:J. You don't, however, reset summary_table_row to row 2 for each worksheet. So, for example, if sheet1 takes 200 rows, then sheet2 will start aggregating information in row 201. If my understanding is correct, then after the code Next i, try adding a line for summary_table_row = 2
– user3561813
Nov 19 at 16:50

Thanks for your response. There is similar data in each tab, and the data in each tab should aggregate in columns "J". The problem is that this aggregateation occurs only on the first tab, and does not loop into the other tabs.
– Earl
Nov 19 at 18:16

Did you try what I recommended? You have to reset the summary_table_row variable for each sheet.
– user3561813
Nov 19 at 18:35

summary_table_Row = 2 should be just above For i = 2 To 99999
– Tim Williams
Nov 19 at 18:51

Bingo, Bingo! Thank you so, so much. If you are in the US, please have a happy Thanksgiving!
– Earl
Nov 19 at 19:53

Is it possible that the data is aggregating, but just below off screen? From reading your code, it seems that each sheet totals information in columns I:J. You don't, however, reset summary_table_row to row 2 for each worksheet. So, for example, if sheet1 takes 200 rows, then sheet2 will start aggregating information in row 201. If my understanding is correct, then after the code Next i, try adding a line for summary_table_row = 2
– user3561813
Nov 19 at 16:50

Thanks for your response. There is similar data in each tab, and the data in each tab should aggregate in columns "J". The problem is that this aggregateation occurs only on the first tab, and does not loop into the other tabs.
– Earl
Nov 19 at 18:16

Did you try what I recommended? You have to reset the summary_table_row variable for each sheet.
– user3561813
Nov 19 at 18:35

summary_table_Row = 2 should be just above For i = 2 To 99999
– Tim Williams
Nov 19 at 18:51

Bingo, Bingo! Thank you so, so much. If you are in the US, please have a happy Thanksgiving!
– Earl
Nov 19 at 19:53

add a comment |

active

oldest

votes

Your Answer

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: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53378800%2fvba-executing-a-loop-within-each-worksheet%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

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.

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

搜尋此網誌

Argthtjtr