Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA Excel Convert a range from string to integer

Status
Not open for further replies.
dprayner

dprayner

Programmer
Oct 14, 2002
140
0
0
US
Hi Good people. My numbers are being stored as text in a range of cells. I want to be able to convert them to integers via VBA code so far I have this:

For Each C In Selection
Selection.TextToColumns Destination:=Range(Selection), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Next

This comes up with an Run-Time error '1004':
Method 'Range' of object'_Global failed.

I want to be able to code for an unknown range (what ever the user selects). I was thinking aout maybe using CInt( ), but I would still have the problem of referencing the selected range.

Any suggestions? Thanks DAVE
 
Sort by date Sort by votes
Why are you using For Each...Next - Text to Columns works on a range object so there is no need to do it for each cell when you can do it for the whole range in 1 go. Also, Range() takes a string argument so you need to use 'Selection.Address' rather than just 'Selection' as that is a range object
Code: 
Selection.TextToColumns Destination:=Range(Selection[b].Address[/b]), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Hi Geoff. So you are saying I should remove the For Next loop? How would I cause the cell or range of cells to receive the new formatting? This work as far as converting the number from text to number, but now I am trying to format it as a date. I have the statements (ie. left, mid, right with / / ) all setup, but how do I reference the cell or range of cells to reformat (ie.
D = Left(C, 2) & "/" & Mid(C, 3, 2) & "/" & Right(C, 2)
Range(?)= D
Range(?).NumberFormat = "m/d/yyyy")

Thanks DAVE
 
Upvote 0 Downvote


Checl out Why do Dates and Times seem to be so much trouble? faq68-5827 before your proceed.

You should NOT enter your dates as STRINGS.

Convert your values to a REAL DATE using the DATE Function.
[tt]
=DATE(year,month,day)
[/tt]
The, as Geoff recommended, you can format the entire range in one fell swoop!


Skip,
[sub]
[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue][/sub]
 
Upvote 0 Downvote
As per my 1st reply, 'Selection' is a RANGE object and therefore has the same properties as a range. You can select multiple cells in excel and format them in 1 go manually so it stands to reason that you can do the same in code...

Selection.numberformat = "dd/mm/yyyy"

perhaps...depending on what date format you want

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Hi Geoff. I am getting closer! It seems that the Spreadsheet is treating the converted numbers as julian numbers and formatting the wrong date (ie Text: 030305 --> Number: --> 30305 --> Julian: 12/20/1982. I wrote a function with the left, mid, and right functions to divide the number and add /s, I just can't seem to reference the cells in the range to put the string result into (ie.
D = Left(C, 2) & "/" & Mid(C, 3, 2) & "/" & Right(C, 2)
Range(?)= D)

This works when I reference a single cell (ie. A1), but the cell or range need to be determined by the user.

I think the key is referencing the range and placing the contents of D is each cell.

Thanks DAVE

 
Upvote 0 Downvote


Dave,

Did you read the FAQ I posted. It will answer the DATE issue and why...

[red]D = Left(C, 2) & "/" & Mid(C, 3, 2) & "/" & Right(C, 2)[/red]

is NOT A REAL DATE!

Skip,
[sub]
[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue][/sub]
 
Upvote 0 Downvote
Hi SkipVought. I read the FAQ you linked to. I don't think it really applies to my situation. The date (ie. 090105) is stored as text. I can convert to a number (90105), but it's not a Julian date. That's why I wrote the code to convert 90105 to 9/01/05. I just need to know how to reference each cell in a range and put the new string (date) value (see above).
 
Upvote 0 Downvote

if
[tt]
A1: 090105
[/tt]
then your REAL DATE is
[tt]
=DATE(IF(RIGHT(A1,2)>"30",1900,2000)+RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))
[/tt]
Otherwise its just a string.

Won't sort corectly!

Won't calculate

It just looks like a date -- kinda Stealth -- but it AIN'T

Skip,
[sub]
[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue][/sub]
 
Upvote 0 Downvote
ummm

090105 is not a Julian date IMO (90105 may be however...)

what is is, is a date without the "/". This cannot be converted to a date that excel would recognise via text to columns - to do that it MUST be in a standard date format such as dd/mm/yyyy or dd-mm-yyyy or dd mmm yyyy. Neither can this string be formatted as a date.

You must 1st use a formula like Skip (accidently) suggested a coupla posts ago:

=Value(Left(A1, 2) & "/" & Mid(A1, 3, 2) & "/" & Right(A1, 2))

This will convert your string to a date - you can then format as whatever kind of date you want. If you want to loop through each cell and apply the formula, you can do that but personally, I'd go for putting the formula in a 2nd column and then copy / paste special values and delete the original column...

where your date is in A1


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Just curious, but if your data looks like the following:-

030305
040305
050305
060305
180305
220305
260305
etc

Then in line with Skips original suggestion, does Data / Text to Columns / Fixed width / --> Next --> Next --> Date (& DMY) not give you what you want. Works fine with UK dates in the sample I just tried?

Regards
Ken............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
Dammit Ken - you just had to prove me wrong didn't you :p

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
LOL :)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
and yes - tested that and, surprisingly enough to me, it worked perfectly. Wouldn't've thought even Excel would've managed that !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Only worry bead I would have is something you alluded to earlier, in that my sample data was all 6 characters long, so days and months were all double digit - Am assuming this is the same with the OPs data, but no idea what would happen if it were not :)

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
602
dhookom
dhookom
Andrzejek
  • Question
Outlook VBA - Trust access
Replies
3
Views
219
hermanlaksko
hermanlaksko
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
127
combo
combo
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
161
mikrom
mikrom
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
157
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top