Copying number using VBA

[nebffa]

Hi all, I have cells containing 'strings that look like numbers' - e.g. 04012

I have this line in my code:

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = .Cells(J, 3 * (I + 1) - 2).Value

The problem is, when assigning the value if the old cell is 04012 the new cell will be 4012 as Excel seems to treat it as a number rather than a string. There are many simple workarounds I could craft for this, for example just chucking a "%" before each value before I copy it, then removing the "%" afterwards, but I want to learn a lot about Excel so is there some other way I can do this directly?

 

[Rick Rothstein]

Try copying the cell format from the source to the destination first...

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).NumberFormat = .Cells(J, 3 * (I + 1) - 2).NumberFormat
Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = .Cells(J, 3 * (I + 1) - 2).Value

 

[Michael M]

AS you haven't provided all the code, I'll assume the line posted is correct
You could try

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = "'" & .Cells(J, 3 * (I + 1) - 2).Value

which puts an apostrophe in front of the value, making it text.

 

[nebffa]

Try copying the cell format from the source to the destination first...

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).NumberFormat = .Cells(J, 3 * (I + 1) - 2).NumberFormat
Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = .Cells(J, 3 * (I + 1) - 2).Value

Oddly, this doesn't work!

AS you haven't provided all the code, I'll assume the line posted is correct
You could try

Sheets("Working").Cells(SchoolCodeStackHeight + 1, 1).Value = "'" & .Cells(J, 3 * (I + 1) - 2).Value

which puts an apostrophe in front of the value, making it text.

Unfortunately, this doesn't work either!

Yes, the code runs correctly, I only didn't post the code because I thought it would clutter the question as it's irrelevant to this particular line.


I would've thought either of these solutions would work but Excel says no.

 

[Rick Rothstein]

Oddly, this doesn't work!

Unfortunately, this doesn't work either!

Describe "doesn't work"... exactly what happens when it does not work?

 

[nebffa]

Ah oops, I should be a little more explicit about that. When it "doesn't work", it works exactly the same as it did without your code, and the same goes for Michael M's suggestion. So the code runs without error, but we still have the same problem as before.

 

[Rick Rothstein]

Ah oops, I should be a little more explicit about that. When it "doesn't work", it works exactly the same as it did without your code, and the same goes for Michael M's suggestion. So the code runs without error, but we still have the same problem as before.

Do you have any On Error statements (especially, Resume Next) in effect? If so, turn them off and re-run the code and tell us of any errors.

 

[nebffa]

Nope there's no On Error/Resume Next or related inside this function. The only thing like that in the whole module is

    Application.DisplayAlerts = False
    Sheets("Working").Delete
    Application.DisplayAlerts = True

which is used so I can delete a worksheet. But as you can see display alerts is turned back on straight away. But nothing that actually handles errors

 

[Rick Rothstein]

Nope there's no On Error/Resume Next or related inside this function. The only thing like that in the whole module is

    Application.DisplayAlerts = False
    Sheets("Working").Delete
    Application.DisplayAlerts = True

which is used so I can delete a worksheet. But as you can see display alerts is turned back on straight away. But nothing that actually handles errors

Okay, I think we are at the point where we will have to physically see the workbook so we can debug it directly. Any chance you can make it available either via your SkyDrive (assuming you have one) or a free file-sharing website (this one works well - http://www.box.net/files; remember to post the URL they give you)? If not, can you send a copy directly to me (this is less desirable as there are better debugger volunteers on this forum than me)? If so, my email is rickDOTnewsATverizonDOTnet (substitute the obvious for the obvious).

 

[Michael M]

Are the "strings" downloaded data from an external source ?
Both Ricks and my code work when tested.
So, I'd suggest either posting all of the code for that particular macro
OR
posting a small sample of data using the HTML maker from the 2nd line of my tag