CODEWITHSUNDEEP
stringvbazero
Jakob Eltz
Jakob Eltz

Reputation: 23

VBA eats my zeros INSIDE a string?

So here is a problem that I have never come across before. I Import an ISIN (for example DE0002635307) from a cell, which is defined as a text. I need to use this to reference to a cell by that name. So:

sub ISINWriter

dim ISIN as String

ISIN = ThisWorkbook.Sheets(i).Cells(j, 4).Value()

ThisWorkbook.Sheets(i+1).Cells(f, 4).Formula = "=" & ISIN

End Sub

For most of the ISINs this works fine, except if there are 4 or more zeros in a row. If that happens - for example FR0000120073 - it writes "=FR120073" into the cell. It just eats the zeros INSIDE the string! Any ideas?

I use Excel 2010 and Windows 7.

Thanks a lot.

Upvotes: 2

Views: 146

Answers (2)

Mike Woodhouse
Mike Woodhouse

Reputation: 52326

If you use the Name Manager to try to create a named range (or more accurately a named formula) called "FR0000120073" then you should find that you get an error. Something like (from Excel 2007 on my machine):

The name that you entered is not valid.
Reasons for this can include:
- The name does not begin with a letter or an underscore
- The name contains a space or other invalid characters
- The name conflicts with an Excel built-in name or the name of another object in the workbook

The clue is in the last part of the third reason. FR120073 is a valid cell address in these days of 16Kx1m cell worksheets.

The first reason above may be useful, though: _FR0000120073 is a valid name. Could you use that?

Upvotes: 2

Purplegoldfish
Purplegoldfish

Reputation: 5294

EDIT: Removing my last answer so this makes sense.

Basically your cell references are an AlphaNumeric value. Take A1 for example A is the column 1 is the row. This issue is occuring because 0001 is the same as 1. so a reference to cell A001 is going to be the same as cell A1.

It looks like Excel has some built in functionality to remove leading 0's from your references to cells.

Upvotes: 1

Related Questions