How to loop through my variant array for count followed by calculation?
0
I have created a variant array list of unique items in Sheet1 from master sheet (Sheet3). Now I am not sure how to copy each item in my array and paste it in sheet4 in column A and a count of that item from sheet "January" next to it in column B. If I can do that, I can then perform some calculation. Your help will be greatly appreciated
I'm basically trying to build Management Information stats, getting count of items we manage and then work out the percentage of that completed on time.
here's what I have done so far to get a variant array list.
Sub PopulatingArrayVariable_v3()
Dim myarray() As Variant
Dim DataRange As range
Dim Cell As range
Dim x As Long
Dim iReport As Integer
Dim lastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = Sheets("Sheet3").range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet3")
.range("D2:D" & lastRow).AdvancedFilter Action:=xlFilterCopy,
copytorange:=Sheets("Sheet1").range("A1"), Unique:=True
End With
Set DataRange = Sheets("Sheet1").UsedRange
For Each Cell In DataRange.Cells
ReDim Preserve myarray(x)
myarray(x) = Cell.Value
x = x + 1
Next Cell
For x = LBound(myarray) To UBound(myarray)
Debug.Print myarray(x)
Next x
iReport = WorksheetFunction.countif(Sheets("January").range("C6:C100"),
myarray(6))
Sheets("Sheet4").Activate
range("A2") = myarray(6)
range("B2") = iReport
//do calculation in % and rounded to 0 decimal point
Application.ScreenUpdating = False
Application.DisplayAlerts = False
End Sub
here's example how I want the end table to look like
Items Count
EMT - Pre Production 4
EMT - Pre Production 5
EMT - Production 10
EMT - Post Production 1
EPT - Pre Production 20
EPT - Production 10
EPT - Post Production 5
AIFMD - Pre Production 25
AIFMD - Production 12
AIFMD - Post Production 12
excel vba loops
edited Jan 2 at 15:05
the scion
814616
asked Jan 2 at 14:54
NasNas
11
add a comment |
0
I have created a variant array list of unique items in Sheet1 from master sheet (Sheet3). Now I am not sure how to copy each item in my array and paste it in sheet4 in column A and a count of that item from sheet "January" next to it in column B. If I can do that, I can then perform some calculation. Your help will be greatly appreciated
I'm basically trying to build Management Information stats, getting count of items we manage and then work out the percentage of that completed on time.
here's what I have done so far to get a variant array list.
Sub PopulatingArrayVariable_v3()
Dim myarray() As Variant
Dim DataRange As range
Dim Cell As range
Dim x As Long
Dim iReport As Integer
Dim lastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = Sheets("Sheet3").range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet3")
.range("D2:D" & lastRow).AdvancedFilter Action:=xlFilterCopy,
copytorange:=Sheets("Sheet1").range("A1"), Unique:=True
End With
Set DataRange = Sheets("Sheet1").UsedRange
For Each Cell In DataRange.Cells
ReDim Preserve myarray(x)
myarray(x) = Cell.Value
x = x + 1
Next Cell
For x = LBound(myarray) To UBound(myarray)
Debug.Print myarray(x)
Next x
iReport = WorksheetFunction.countif(Sheets("January").range("C6:C100"),
myarray(6))
Sheets("Sheet4").Activate
range("A2") = myarray(6)
range("B2") = iReport
//do calculation in % and rounded to 0 decimal point
Application.ScreenUpdating = False
Application.DisplayAlerts = False
End Sub
here's example how I want the end table to look like
Items Count
EMT - Pre Production 4
EMT - Pre Production 5
EMT - Production 10
EMT - Post Production 1
EPT - Pre Production 20
EPT - Production 10
EPT - Post Production 5
AIFMD - Pre Production 25
AIFMD - Production 12
AIFMD - Post Production 12
excel vba loops
edited Jan 2 at 15:05
the scion
814616
asked Jan 2 at 14:54
NasNas
11
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could useCountIfas you have done in your code). To paste an array to your range, use Transpose
– Zac
Jan 2 at 15:07
3
Why not use a pivot table?
– SJR
Jan 2 at 15:08
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06
add a comment |
0
0
0
I have created a variant array list of unique items in Sheet1 from master sheet (Sheet3). Now I am not sure how to copy each item in my array and paste it in sheet4 in column A and a count of that item from sheet "January" next to it in column B. If I can do that, I can then perform some calculation. Your help will be greatly appreciated
I'm basically trying to build Management Information stats, getting count of items we manage and then work out the percentage of that completed on time.
here's what I have done so far to get a variant array list.
Sub PopulatingArrayVariable_v3()
Dim myarray() As Variant
Dim DataRange As range
Dim Cell As range
Dim x As Long
Dim iReport As Integer
Dim lastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = Sheets("Sheet3").range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet3")
.range("D2:D" & lastRow).AdvancedFilter Action:=xlFilterCopy,
copytorange:=Sheets("Sheet1").range("A1"), Unique:=True
End With
Set DataRange = Sheets("Sheet1").UsedRange
For Each Cell In DataRange.Cells
ReDim Preserve myarray(x)
myarray(x) = Cell.Value
x = x + 1
Next Cell
For x = LBound(myarray) To UBound(myarray)
Debug.Print myarray(x)
Next x
iReport = WorksheetFunction.countif(Sheets("January").range("C6:C100"),
myarray(6))
Sheets("Sheet4").Activate
range("A2") = myarray(6)
range("B2") = iReport
//do calculation in % and rounded to 0 decimal point
Application.ScreenUpdating = False
Application.DisplayAlerts = False
End Sub
here's example how I want the end table to look like
Items Count
EMT - Pre Production 4
EMT - Pre Production 5
EMT - Production 10
EMT - Post Production 1
EPT - Pre Production 20
EPT - Production 10
EPT - Post Production 5
AIFMD - Pre Production 25
AIFMD - Production 12
AIFMD - Post Production 12
excel vba loops
edited Jan 2 at 15:05
the scion
814616
asked Jan 2 at 14:54
NasNas
11
I have created a variant array list of unique items in Sheet1 from master sheet (Sheet3). Now I am not sure how to copy each item in my array and paste it in sheet4 in column A and a count of that item from sheet "January" next to it in column B. If I can do that, I can then perform some calculation. Your help will be greatly appreciated
I'm basically trying to build Management Information stats, getting count of items we manage and then work out the percentage of that completed on time.
here's what I have done so far to get a variant array list.
Sub PopulatingArrayVariable_v3()
Dim myarray() As Variant
Dim DataRange As range
Dim Cell As range
Dim x As Long
Dim iReport As Integer
Dim lastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
lastRow = Sheets("Sheet3").range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet3")
.range("D2:D" & lastRow).AdvancedFilter Action:=xlFilterCopy,
copytorange:=Sheets("Sheet1").range("A1"), Unique:=True
End With
Set DataRange = Sheets("Sheet1").UsedRange
For Each Cell In DataRange.Cells
ReDim Preserve myarray(x)
myarray(x) = Cell.Value
x = x + 1
Next Cell
For x = LBound(myarray) To UBound(myarray)
Debug.Print myarray(x)
Next x
iReport = WorksheetFunction.countif(Sheets("January").range("C6:C100"),
myarray(6))
Sheets("Sheet4").Activate
range("A2") = myarray(6)
range("B2") = iReport
//do calculation in % and rounded to 0 decimal point
Application.ScreenUpdating = False
Application.DisplayAlerts = False
End Sub
here's example how I want the end table to look like
Items Count
EMT - Pre Production 4
EMT - Pre Production 5
EMT - Production 10
EMT - Post Production 1
EPT - Pre Production 20
EPT - Production 10
EPT - Post Production 5
AIFMD - Pre Production 25
AIFMD - Production 12
AIFMD - Post Production 12
excel vba loops
excel vba loops
edited Jan 2 at 15:05
the scion
814616
asked Jan 2 at 14:54
NasNas
11
edited Jan 2 at 15:05
the scion
814616
asked Jan 2 at 14:54
NasNas
11
edited Jan 2 at 15:05
the scion
814616
edited Jan 2 at 15:05
the scion
814616
edited Jan 2 at 15:05
the scion
814616
814616
asked Jan 2 at 14:54
NasNas
11
asked Jan 2 at 14:54
NasNas
11
asked Jan 2 at 14:54
NasNas
11
11
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could useCountIfas you have done in your code). To paste an array to your range, use Transpose
– Zac
Jan 2 at 15:07
3
Why not use a pivot table?
– SJR
Jan 2 at 15:08
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06
add a comment |
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could useCountIfas you have done in your code). To paste an array to your range, use Transpose
– Zac
Jan 2 at 15:07
3
Why not use a pivot table?
– SJR
Jan 2 at 15:08
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could use
CountIf as you have done in your code). To paste an array to your range, use Transpose– Zac
Jan 2 at 15:07
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could use
CountIf as you have done in your code). To paste an array to your range, use Transpose– Zac
Jan 2 at 15:07
3
3
Why not use a pivot table?
– SJR
Jan 2 at 15:08
Why not use a pivot table?
– SJR
Jan 2 at 15:08
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06
add a comment |
0
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',
autoActivateHeartbeat: false,
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008472%2fhow-to-loop-through-my-variant-array-for-count-followed-by-calculation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
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.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008472%2fhow-to-loop-through-my-variant-array-for-count-followed-by-calculation%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
For count, I would suggest that you just use a formula in column B as you already have a unique list (you could use
CountIfas you have done in your code). To paste an array to your range, use Transpose– Zac
Jan 2 at 15:07
3
Why not use a pivot table?
– SJR
Jan 2 at 15:08
thanks. the countif line of code i've used will be lenthly for current array lists contain 97 items. Need this in a loop so it's dynamic and I am not really after count but using this count to work out the percentage over total count of items with prefix say EMT.
– Nas
Jan 2 at 16:06