Copying number using VBA

nebffa

Board Regular
Joined
Apr 20, 2012
Messages
55
Hi all, I have cells containing 'strings that look like numbers' - e.g. 04012

I have this line in my code:

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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
AS you haven't provided all the code, I'll assume the line posted is correct
You could try
Code:
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

Board Regular
Joined
Apr 20, 2012
Messages
55
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
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Oddly, this doesn't work!

Unfortunately, this doesn't work either!
Describe "doesn't work"... exactly what happens when it does not work?
 

nebffa

Board Regular
Joined
Apr 20, 2012
Messages
55
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Apr 20, 2012
Messages
55
Nope there's no On Error/Resume Next or related inside this function. The only thing like that in the whole module is

Code:
    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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
Nope there's no On Error/Resume Next or related inside this function. The only thing like that in the whole module is

Code:
    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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,807
Messages
5,638,478
Members
417,026
Latest member
UDK

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
Top