VBA code not working in Excel with dataset
I am working on a machine learning project and am using Excel to handle the dataset. I am new to both Excel and VBA.
So I am using this dataset, and I just copy pasted the whole thing into an excel spreadsheet. I did text to columns. Here's a snapshot of some of the data:
Snapshot of data
I want to reformat the data in the spreadsheet so that all of the data goes into a single row, then starts a new row after the "name" keyword.
For example, I want this:
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 name
to become:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 name (all on one line)
without having to do it manually line by line.
I used the below VBA code to format the data how I want it:
Sub separateByName()
Dim lRow As Long
Dim lCol As Long
Dim lCol2 As Long
k = 1
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow
lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
lCol2 = Sheets("Sheet2").Cells(k, Columns.Count).End(xlToLeft).Column
Sheets("Sheet2").Cells(k, lCol2 + 1).Value = Cells(i, j).Value
If Cells(i, j).Value = "name" Then k = k + 1
Next j
Next i
End Sub
However, when I run I'm getting problems in that the result seems randomly patterned.
This:
1 0 63 1 -9 -9 -9
-9 1 145 1 233 -9 50 20
1 -9 1 2 2 3 81 0
0 0 0 0 1 10.5 6 13
150 60 190 90 145 85 0 0
2.3 3 -9 172 0 -9 -9 -9
-9 -9 -9 6 -9 -9 -9 2
16 81 0 1 1 1 -9 1
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
2 0 67 1 -9 -9 -9
-9 4 160 1 286 -9 40 40
0 -9 1 2 3 5 81 0
1 0 0 0 1 9.5 6 13
108 64 160 90 160 90 1 0
1.5 2 -9 185 3 -9 -9 -9
-9 -9 -9 3 -9 -9 -9 2
5 81 2 1 2 2 -9 2
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
Became this:
1 0 63 1 -9 -9 -9 1 0 63 1 -9 -9 -9 -9 1 145 1 233 -9 50 20 1 -9 1 2 2 3 81 0 0 0 0 0 1 10.5 6 13 150 60 190 90 145 85 0 0 2.3 3 -9 172 0 -9 -9 -9 -9 -9 -9 6 -9 -9 -9 2 16 81 0 1 1 1 -9 1 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
-9 1 145 1 233 -9 50 20 2 0 67 1 -9 -9 -9 -9 4 160 1 286 -9 40 40 0 -9 1 2 3 5 81 0 1 0 0 0 1 9.5 6 13 108 64 160 90 160 90 1 0 1.5 2 -9 185 3 -9 -9 -9 -9 -9 -9 3 -9 -9 -9 2 5 81 2 1 2 2 -9 2 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
The "name" is correctly at the end, but the actual data is messed up.
Could anyone help me to fix this code for my dataset?
Thanks!
excel vba format dataset
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
add a comment |
I am working on a machine learning project and am using Excel to handle the dataset. I am new to both Excel and VBA.
So I am using this dataset, and I just copy pasted the whole thing into an excel spreadsheet. I did text to columns. Here's a snapshot of some of the data:
Snapshot of data
I want to reformat the data in the spreadsheet so that all of the data goes into a single row, then starts a new row after the "name" keyword.
For example, I want this:
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 name
to become:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 name (all on one line)
without having to do it manually line by line.
I used the below VBA code to format the data how I want it:
Sub separateByName()
Dim lRow As Long
Dim lCol As Long
Dim lCol2 As Long
k = 1
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow
lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
lCol2 = Sheets("Sheet2").Cells(k, Columns.Count).End(xlToLeft).Column
Sheets("Sheet2").Cells(k, lCol2 + 1).Value = Cells(i, j).Value
If Cells(i, j).Value = "name" Then k = k + 1
Next j
Next i
End Sub
However, when I run I'm getting problems in that the result seems randomly patterned.
This:
1 0 63 1 -9 -9 -9
-9 1 145 1 233 -9 50 20
1 -9 1 2 2 3 81 0
0 0 0 0 1 10.5 6 13
150 60 190 90 145 85 0 0
2.3 3 -9 172 0 -9 -9 -9
-9 -9 -9 6 -9 -9 -9 2
16 81 0 1 1 1 -9 1
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
2 0 67 1 -9 -9 -9
-9 4 160 1 286 -9 40 40
0 -9 1 2 3 5 81 0
1 0 0 0 1 9.5 6 13
108 64 160 90 160 90 1 0
1.5 2 -9 185 3 -9 -9 -9
-9 -9 -9 3 -9 -9 -9 2
5 81 2 1 2 2 -9 2
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
Became this:
1 0 63 1 -9 -9 -9 1 0 63 1 -9 -9 -9 -9 1 145 1 233 -9 50 20 1 -9 1 2 2 3 81 0 0 0 0 0 1 10.5 6 13 150 60 190 90 145 85 0 0 2.3 3 -9 172 0 -9 -9 -9 -9 -9 -9 6 -9 -9 -9 2 16 81 0 1 1 1 -9 1 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
-9 1 145 1 233 -9 50 20 2 0 67 1 -9 -9 -9 -9 4 160 1 286 -9 40 40 0 -9 1 2 3 5 81 0 1 0 0 0 1 9.5 6 13 108 64 160 90 160 90 1 0 1.5 2 -9 185 3 -9 -9 -9 -9 -9 -9 3 -9 -9 -9 2 5 81 2 1 2 2 -9 2 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
The "name" is correctly at the end, but the actual data is messed up.
Could anyone help me to fix this code for my dataset?
Thanks!
excel vba format dataset
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09
add a comment |
I am working on a machine learning project and am using Excel to handle the dataset. I am new to both Excel and VBA.
So I am using this dataset, and I just copy pasted the whole thing into an excel spreadsheet. I did text to columns. Here's a snapshot of some of the data:
Snapshot of data
I want to reformat the data in the spreadsheet so that all of the data goes into a single row, then starts a new row after the "name" keyword.
For example, I want this:
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 name
to become:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 name (all on one line)
without having to do it manually line by line.
I used the below VBA code to format the data how I want it:
Sub separateByName()
Dim lRow As Long
Dim lCol As Long
Dim lCol2 As Long
k = 1
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow
lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
lCol2 = Sheets("Sheet2").Cells(k, Columns.Count).End(xlToLeft).Column
Sheets("Sheet2").Cells(k, lCol2 + 1).Value = Cells(i, j).Value
If Cells(i, j).Value = "name" Then k = k + 1
Next j
Next i
End Sub
However, when I run I'm getting problems in that the result seems randomly patterned.
This:
1 0 63 1 -9 -9 -9
-9 1 145 1 233 -9 50 20
1 -9 1 2 2 3 81 0
0 0 0 0 1 10.5 6 13
150 60 190 90 145 85 0 0
2.3 3 -9 172 0 -9 -9 -9
-9 -9 -9 6 -9 -9 -9 2
16 81 0 1 1 1 -9 1
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
2 0 67 1 -9 -9 -9
-9 4 160 1 286 -9 40 40
0 -9 1 2 3 5 81 0
1 0 0 0 1 9.5 6 13
108 64 160 90 160 90 1 0
1.5 2 -9 185 3 -9 -9 -9
-9 -9 -9 3 -9 -9 -9 2
5 81 2 1 2 2 -9 2
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
Became this:
1 0 63 1 -9 -9 -9 1 0 63 1 -9 -9 -9 -9 1 145 1 233 -9 50 20 1 -9 1 2 2 3 81 0 0 0 0 0 1 10.5 6 13 150 60 190 90 145 85 0 0 2.3 3 -9 172 0 -9 -9 -9 -9 -9 -9 6 -9 -9 -9 2 16 81 0 1 1 1 -9 1 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
-9 1 145 1 233 -9 50 20 2 0 67 1 -9 -9 -9 -9 4 160 1 286 -9 40 40 0 -9 1 2 3 5 81 0 1 0 0 0 1 9.5 6 13 108 64 160 90 160 90 1 0 1.5 2 -9 185 3 -9 -9 -9 -9 -9 -9 3 -9 -9 -9 2 5 81 2 1 2 2 -9 2 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
The "name" is correctly at the end, but the actual data is messed up.
Could anyone help me to fix this code for my dataset?
Thanks!
excel vba format dataset
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
I am working on a machine learning project and am using Excel to handle the dataset. I am new to both Excel and VBA.
So I am using this dataset, and I just copy pasted the whole thing into an excel spreadsheet. I did text to columns. Here's a snapshot of some of the data:
Snapshot of data
I want to reformat the data in the spreadsheet so that all of the data goes into a single row, then starts a new row after the "name" keyword.
For example, I want this:
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 name
to become:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 name (all on one line)
without having to do it manually line by line.
I used the below VBA code to format the data how I want it:
Sub separateByName()
Dim lRow As Long
Dim lCol As Long
Dim lCol2 As Long
k = 1
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow
lCol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 1 To lCol
lCol2 = Sheets("Sheet2").Cells(k, Columns.Count).End(xlToLeft).Column
Sheets("Sheet2").Cells(k, lCol2 + 1).Value = Cells(i, j).Value
If Cells(i, j).Value = "name" Then k = k + 1
Next j
Next i
End Sub
However, when I run I'm getting problems in that the result seems randomly patterned.
This:
1 0 63 1 -9 -9 -9
-9 1 145 1 233 -9 50 20
1 -9 1 2 2 3 81 0
0 0 0 0 1 10.5 6 13
150 60 190 90 145 85 0 0
2.3 3 -9 172 0 -9 -9 -9
-9 -9 -9 6 -9 -9 -9 2
16 81 0 1 1 1 -9 1
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
2 0 67 1 -9 -9 -9
-9 4 160 1 286 -9 40 40
0 -9 1 2 3 5 81 0
1 0 0 0 1 9.5 6 13
108 64 160 90 160 90 1 0
1.5 2 -9 185 3 -9 -9 -9
-9 -9 -9 3 -9 -9 -9 2
5 81 2 1 2 2 -9 2
-9 1 -9 1 1 1 1 1
1 1 -9 -9 name
Became this:
1 0 63 1 -9 -9 -9 1 0 63 1 -9 -9 -9 -9 1 145 1 233 -9 50 20 1 -9 1 2 2 3 81 0 0 0 0 0 1 10.5 6 13 150 60 190 90 145 85 0 0 2.3 3 -9 172 0 -9 -9 -9 -9 -9 -9 6 -9 -9 -9 2 16 81 0 1 1 1 -9 1 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
-9 1 145 1 233 -9 50 20 2 0 67 1 -9 -9 -9 -9 4 160 1 286 -9 40 40 0 -9 1 2 3 5 81 0 1 0 0 0 1 9.5 6 13 108 64 160 90 160 90 1 0 1.5 2 -9 185 3 -9 -9 -9 -9 -9 -9 3 -9 -9 -9 2 5 81 2 1 2 2 -9 2 -9 1 -9 1 1 1 1 1 1 1 -9 -9 name
The "name" is correctly at the end, but the actual data is messed up.
Could anyone help me to fix this code for my dataset?
Thanks!
excel vba format dataset
excel vba format dataset
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
asked Nov 26 '18 at 4:59
Kasy ChakraKasy Chakra
85
85
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09
add a comment |
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09
add a comment |
1 Answer
1
active
oldest
votes
I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.
answered Nov 26 '18 at 5:38
MikaelMikael
403
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
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%2f53475000%2fvba-code-not-working-in-excel-with-dataset%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
I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.
answered Nov 26 '18 at 5:38
MikaelMikael
403
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
add a comment |
I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.
answered Nov 26 '18 at 5:38
MikaelMikael
403
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
add a comment |
I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.
answered Nov 26 '18 at 5:38
MikaelMikael
403
I also tested your code with data and i got it to work just fine, just make sure on sheet 1 you have the data and you have empty sheet 2, then use the macro while sheet 1 is open. then your data is in sheet 2.
answered Nov 26 '18 at 5:38
MikaelMikael
403
answered Nov 26 '18 at 5:38
MikaelMikael
403
answered Nov 26 '18 at 5:38
MikaelMikael
403
answered Nov 26 '18 at 5:38
MikaelMikael
403
403
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
add a comment |
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
Thank you so much! Yes as I said I am a beginner with this haha.
– Kasy Chakra
Nov 26 '18 at 19:09
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.
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%2f53475000%2fvba-code-not-working-in-excel-with-dataset%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
I've tested your code with the data you have provided and it's working fine. Can't duplicate your problem.
– Michal Rosa
Nov 26 '18 at 5:09