Type Mismatch in code to populate a combobox
This code was working perfectly yesterday but now I get a type mismatch run time error 13. I have tried to research the issue but don't understand vba well enough to solve it.
All I have changed today is some sheet names which I have now undone but it still will not work.
Dim WKB As Workbook
Dim SHT_data_WORKERS As Worksheet
Set WKB = ActiveWorkbook
Set SHT_data_WORKERS = WKB.Sheets("data_WORKERS")
Dim vArr As Variant
Dim i As Integer
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
With form_addTask.form_addTask_Worker
.Clear
For i = LBound(vArr) To UBound(vArr)
.AddItem vArr(i)
Next i
End With
EDIT -
I've narrowed the problem down to the following line;
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
I do not get any errors when there are two or more rows filled in on the SHT_data_WORKERS page.
So this is the structure of the SHT_data_WORKERS page where the list is pulled from during an error;
ID | Name | Role
1 Chris Engineer
However it works when there are two rows entered;
ID | Name | Role
1 Chris Engineer
2 Test Engineer
Does anyone now how to format the code to work when there is only 1 entry?
Thanks
excel vba
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
add a comment |
This code was working perfectly yesterday but now I get a type mismatch run time error 13. I have tried to research the issue but don't understand vba well enough to solve it.
All I have changed today is some sheet names which I have now undone but it still will not work.
Dim WKB As Workbook
Dim SHT_data_WORKERS As Worksheet
Set WKB = ActiveWorkbook
Set SHT_data_WORKERS = WKB.Sheets("data_WORKERS")
Dim vArr As Variant
Dim i As Integer
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
With form_addTask.form_addTask_Worker
.Clear
For i = LBound(vArr) To UBound(vArr)
.AddItem vArr(i)
Next i
End With
EDIT -
I've narrowed the problem down to the following line;
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
I do not get any errors when there are two or more rows filled in on the SHT_data_WORKERS page.
So this is the structure of the SHT_data_WORKERS page where the list is pulled from during an error;
ID | Name | Role
1 Chris Engineer
However it works when there are two rows entered;
ID | Name | Role
1 Chris Engineer
2 Test Engineer
Does anyone now how to format the code to work when there is only 1 entry?
Thanks
excel vba
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another moduleform_addTask.Show
– ChrisBull
Nov 13 '18 at 9:44
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Maybe the problem is not the code itself but it thrown by the code of the form (egInit-event?)
– FunThomas
Nov 13 '18 at 9:48
If i step through the error appears as it tries to do.AddItem vArr(i)
– ChrisBull
Nov 13 '18 at 9:49
add a comment |
This code was working perfectly yesterday but now I get a type mismatch run time error 13. I have tried to research the issue but don't understand vba well enough to solve it.
All I have changed today is some sheet names which I have now undone but it still will not work.
Dim WKB As Workbook
Dim SHT_data_WORKERS As Worksheet
Set WKB = ActiveWorkbook
Set SHT_data_WORKERS = WKB.Sheets("data_WORKERS")
Dim vArr As Variant
Dim i As Integer
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
With form_addTask.form_addTask_Worker
.Clear
For i = LBound(vArr) To UBound(vArr)
.AddItem vArr(i)
Next i
End With
EDIT -
I've narrowed the problem down to the following line;
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
I do not get any errors when there are two or more rows filled in on the SHT_data_WORKERS page.
So this is the structure of the SHT_data_WORKERS page where the list is pulled from during an error;
ID | Name | Role
1 Chris Engineer
However it works when there are two rows entered;
ID | Name | Role
1 Chris Engineer
2 Test Engineer
Does anyone now how to format the code to work when there is only 1 entry?
Thanks
excel vba
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
This code was working perfectly yesterday but now I get a type mismatch run time error 13. I have tried to research the issue but don't understand vba well enough to solve it.
All I have changed today is some sheet names which I have now undone but it still will not work.
Dim WKB As Workbook
Dim SHT_data_WORKERS As Worksheet
Set WKB = ActiveWorkbook
Set SHT_data_WORKERS = WKB.Sheets("data_WORKERS")
Dim vArr As Variant
Dim i As Integer
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
With form_addTask.form_addTask_Worker
.Clear
For i = LBound(vArr) To UBound(vArr)
.AddItem vArr(i)
Next i
End With
EDIT -
I've narrowed the problem down to the following line;
vArr = WorksheetFunction.Transpose(SHT_data_WORKERS.Range("B2:B" & SHT_data_WORKERS.Range("B" & Rows.Count).End(xlUp).Row).Value)
I do not get any errors when there are two or more rows filled in on the SHT_data_WORKERS page.
So this is the structure of the SHT_data_WORKERS page where the list is pulled from during an error;
ID | Name | Role
1 Chris Engineer
However it works when there are two rows entered;
ID | Name | Role
1 Chris Engineer
2 Test Engineer
Does anyone now how to format the code to work when there is only 1 entry?
Thanks
excel vba
excel vba
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
edited Nov 13 '18 at 10:55
ChrisBull
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
asked Nov 13 '18 at 9:38
ChrisBullChrisBull
189215
189215
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another moduleform_addTask.Show
– ChrisBull
Nov 13 '18 at 9:44
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Maybe the problem is not the code itself but it thrown by the code of the form (egInit-event?)
– FunThomas
Nov 13 '18 at 9:48
If i step through the error appears as it tries to do.AddItem vArr(i)
– ChrisBull
Nov 13 '18 at 9:49
add a comment |
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another moduleform_addTask.Show
– ChrisBull
Nov 13 '18 at 9:44
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Maybe the problem is not the code itself but it thrown by the code of the form (egInit-event?)
– FunThomas
Nov 13 '18 at 9:48
If i step through the error appears as it tries to do.AddItem vArr(i)
– ChrisBull
Nov 13 '18 at 9:49
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another module
form_addTask.Show– ChrisBull
Nov 13 '18 at 9:44
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another module
form_addTask.Show– ChrisBull
Nov 13 '18 at 9:44
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Maybe the problem is not the code itself but it thrown by the code of the form (eg
Init-event?)– FunThomas
Nov 13 '18 at 9:48
Maybe the problem is not the code itself but it thrown by the code of the form (eg
Init-event?)– FunThomas
Nov 13 '18 at 9:48
If i step through the error appears as it tries to do
.AddItem vArr(i)– ChrisBull
Nov 13 '18 at 9:49
If i step through the error appears as it tries to do
.AddItem vArr(i)– ChrisBull
Nov 13 '18 at 9:49
add a comment |
1 Answer
1
active
oldest
votes
You probably have invalid values in your sheet, eg a #N/A. Note that this is not a string, Excel handles such error values with an own vartype.
Check if the value is okay before adding it:
If Not IsError(vArr(i)) Then
.AddItem vArr(i)
End If
Update (after you added some more information to your question):
If your data contains only one row, the transpose will get only one cell as parameter (B2:B2). In that case, you get a single value as result, not an array.
So change your code to
With form_addTask.form_addTask_Worker
.Clear
If IsArray(vArr) Then
For i = LBound(vArr) To UBound(vArr)
If Not IsError(vArr(i)) Then .AddItem vArr(i)
Next i
Else
If Not IsError(vArr) Then .AddItem vArr
End If
End With
(of course it's up to you to decide if you want to keep the check for error before adding the item)
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first firstAddItem? What is the content ofvArr(i)at that moment? Does something like.AddItem iwork?
– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
add a comment |
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
});
}
});
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%2f53277959%2ftype-mismatch-in-code-to-populate-a-combobox%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You probably have invalid values in your sheet, eg a #N/A. Note that this is not a string, Excel handles such error values with an own vartype.
Check if the value is okay before adding it:
If Not IsError(vArr(i)) Then
.AddItem vArr(i)
End If
Update (after you added some more information to your question):
If your data contains only one row, the transpose will get only one cell as parameter (B2:B2). In that case, you get a single value as result, not an array.
So change your code to
With form_addTask.form_addTask_Worker
.Clear
If IsArray(vArr) Then
For i = LBound(vArr) To UBound(vArr)
If Not IsError(vArr(i)) Then .AddItem vArr(i)
Next i
Else
If Not IsError(vArr) Then .AddItem vArr
End If
End With
(of course it's up to you to decide if you want to keep the check for error before adding the item)
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first firstAddItem? What is the content ofvArr(i)at that moment? Does something like.AddItem iwork?
– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
add a comment |
You probably have invalid values in your sheet, eg a #N/A. Note that this is not a string, Excel handles such error values with an own vartype.
Check if the value is okay before adding it:
If Not IsError(vArr(i)) Then
.AddItem vArr(i)
End If
Update (after you added some more information to your question):
If your data contains only one row, the transpose will get only one cell as parameter (B2:B2). In that case, you get a single value as result, not an array.
So change your code to
With form_addTask.form_addTask_Worker
.Clear
If IsArray(vArr) Then
For i = LBound(vArr) To UBound(vArr)
If Not IsError(vArr(i)) Then .AddItem vArr(i)
Next i
Else
If Not IsError(vArr) Then .AddItem vArr
End If
End With
(of course it's up to you to decide if you want to keep the check for error before adding the item)
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first firstAddItem? What is the content ofvArr(i)at that moment? Does something like.AddItem iwork?
– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
add a comment |
You probably have invalid values in your sheet, eg a #N/A. Note that this is not a string, Excel handles such error values with an own vartype.
Check if the value is okay before adding it:
If Not IsError(vArr(i)) Then
.AddItem vArr(i)
End If
Update (after you added some more information to your question):
If your data contains only one row, the transpose will get only one cell as parameter (B2:B2). In that case, you get a single value as result, not an array.
So change your code to
With form_addTask.form_addTask_Worker
.Clear
If IsArray(vArr) Then
For i = LBound(vArr) To UBound(vArr)
If Not IsError(vArr(i)) Then .AddItem vArr(i)
Next i
Else
If Not IsError(vArr) Then .AddItem vArr
End If
End With
(of course it's up to you to decide if you want to keep the check for error before adding the item)
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
You probably have invalid values in your sheet, eg a #N/A. Note that this is not a string, Excel handles such error values with an own vartype.
Check if the value is okay before adding it:
If Not IsError(vArr(i)) Then
.AddItem vArr(i)
End If
Update (after you added some more information to your question):
If your data contains only one row, the transpose will get only one cell as parameter (B2:B2). In that case, you get a single value as result, not an array.
So change your code to
With form_addTask.form_addTask_Worker
.Clear
If IsArray(vArr) Then
For i = LBound(vArr) To UBound(vArr)
If Not IsError(vArr(i)) Then .AddItem vArr(i)
Next i
Else
If Not IsError(vArr) Then .AddItem vArr
End If
End With
(of course it's up to you to decide if you want to keep the check for error before adding the item)
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
edited Nov 13 '18 at 12:48
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
answered Nov 13 '18 at 9:57
FunThomasFunThomas
4,5561523
4,5561523
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first firstAddItem? What is the content ofvArr(i)at that moment? Does something like.AddItem iwork?
– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
add a comment |
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first firstAddItem? What is the content ofvArr(i)at that moment? Does something like.AddItem iwork?
– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
no luck - get the same error message. There are only 2 rows of data in the range and both are text values
– ChrisBull
Nov 13 '18 at 10:06
Does the error occurs at the first first
AddItem? What is the content of vArr(i) at that moment? Does something like .AddItem i work?– FunThomas
Nov 13 '18 at 10:10
Does the error occurs at the first first
AddItem? What is the content of vArr(i) at that moment? Does something like .AddItem i work?– FunThomas
Nov 13 '18 at 10:10
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
hi - the question has been updated above to narrow the problem down! Thanks
– ChrisBull
Nov 13 '18 at 10:50
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
@ChrisBull: I updated my answer
– FunThomas
Nov 13 '18 at 12:48
add a comment |
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.
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%2f53277959%2ftype-mismatch-in-code-to-populate-a-combobox%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
The range is only 3 rows at the moment, is there a limit for the transpose function? I'm not sure where the exact error occurs as the debug highlights the line that calls the form in another module
form_addTask.Show– ChrisBull
Nov 13 '18 at 9:44
Ah... that is not the problem then. If you step through with F8 is that still the line which throws the error?
– QHarr
Nov 13 '18 at 9:45
Maybe the problem is not the code itself but it thrown by the code of the form (eg
Init-event?)– FunThomas
Nov 13 '18 at 9:48
If i step through the error appears as it tries to do
.AddItem vArr(i)– ChrisBull
Nov 13 '18 at 9:49