2010 excel and macro adding zero's to data

msmith83

New Member
Joined
Mar 25, 2013
Messages
3
Hello all!
I am in need of a little help here. I've got my macro to run through my data and spit out the data like I want, EXCEPT; I get an extra "0" added into my data.
This is my formulas:
Sheet3.Cells(f, 1).Value = "0" & Sheet2.Cells(j, 1).Value & Sheet2.Cells(j, 2).Value & " " & Sheet2.Cells(j, 3).Value
Sheet3.Cells(g, 2).Value = "0" & Sheet2.Cells(g, 1).Value & Sheet2.Cells(g, 2).Value & Sheet2.Cells(g, 4).Value & " " & Sheet2.Cells(g, 5).Value
This is the data:
01. 10 . 002 test1
01. 10 . 002 .01 Notes
01. 10 . 002 .02 Reviews
01. 10 . 002 .03 Documents
01. 10 . 002 .04 Mat.
01. 10 . 002 .05 Plan
and this is the output:
01.10.006 test5
01.10.0060.01 Notes
01.10.0060.02 Reviews
01.10.0060.03 Documents
01.10.0060.04 Mat.
01.10.0060.05 Plan
As you can see from above, the second set of numbers gets an extra "0" stuck behind it! I've tried changing the formula around, formatting, changing other parts of code that create the numbers; but to no avail. Anyone have any ideas?
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
ty for reformatting the post.

If the 3rd element of data is 002, how does it get to 006 in the output?

What's in Sheet2.Cells(g, 4).Value ?

is it with intent that
Sheet2.Cells(g, 3).Value is skipped?
 
Upvote 0
ty for reformatting the post.

If the 3rd element of data is 002, how does it get to 006 in the output?

What's in Sheet2.Cells(g, 4).Value ?

is it with intent that
Sheet2.Cells(g, 3).Value is skipped?

01.10.002test1
01.10.0020.01 Notes
01.10.0020.02 Reviews
01.10.0020.03 Docs
01.10.0020.04 Mat.
01.10.0020.05 Plan

sorry this was supposed to be the output. I'm creating folders by a column sub structure. In other words test1 is in a main folder. Notes thru plan are in a sub folder under it.
Also g,3 is meant to be skipped. That column has the test1-5 folder names in it. The issue is when I & G1, G2, G4 and G5. All of the sudden on the back of G2 - excel adds an unnesesary 0.

<tbody>
</tbody>
 
Last edited:
Upvote 0
Put your cursor on this line
Sheet3.Cells(g, 2).Value = "0" & Sheet2.Cells(g, 1).Value & Sheet2.Cells(g, 2).Value & Sheet2.Cells(g, 4).Value & " " & Sheet2.Cells(g, 5).Value
and F9 to add a break point.
Then hover over each Cells element and see which one has the extra "0"
Then you know where the value/formatting needs to be addressed.
 
Upvote 0
Actually, I figured it out earlier today. Since I was trying to add all the columns together, excel wanted to treat it like a number I guess. I ended up putting in my macro a " ' " before the data. It turned them all into strings and excel was happy.
 
Upvote 0

Forum statistics

Threads
1,203,233
Messages
6,054,275
Members
444,714
Latest member
excel2782

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top