Excel VBA Convert To Number issue 2

[JasonEnsor]

Hi Guys,

I am working on some data from our PeopleSoft database in Excel 2010. It's nothing that really warrents coding as it is mainly using CountIF formulas. My issue is that many of the columns have Integer values that when they are being recognised by excel as text. This is giving me the little green corner on my cell where i am having to select to treat this as a number.

I have tried recording a macro for this and all i get is that i have selected a cell or a range. I can easily write code to loop through every cell in a column but i am not sure what command i will need to use to convert this integer that is being treated as text, to an integer that excel notices as an integer.

I know i could do this manually, but i would rather automate the process if possible...afterall what kind of programmer would i be if i just accepted that i had to do manual tasks?

Many Thanks

J.

 

[SkipVought]

hi,

How does PeopleSoft export this data, ie what kind of file?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

If the cells are not formatted as text, I select a range and run the following:
Sub ReenterCells()
Dim c As Range
On Error Resume Next
For Each c In Selection
c.Formula = c.Formula
Next c
End Sub

combo

 

[JasonEnsor]

Hi Skip,

It is exported as an Excel 2003 file.



Regards

J.

 

[SkipVought]

then that process has a problem.

A simple way would be to COPY a 1, select the column of data and Edit > Paste Special MULTIPLY.

I might make a workbook application that queries the people soft workbook into a standard table location (sheet) where this cleanup would take place.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JasonEnsor]

Hi Combo,

That works brilliantly. I didn't realise it was going to be something so simple.

Many Thanks

Regards

J.

 

[JasonEnsor]

Hi Skip,

Thanks for the suggestion, I think this is something i might be doing quite a bit over the coming months so i will deffinatly look in to it. Looking at a few reports, i think the issue is that the column i am converting to an integer is exported as a string from PeopleSoft as we have not only numerical grades but also the data can contain 'P' or 'F' for Pass/Fail components. Using CountIf i am able to count the number of occurences of 'P' and 'F' but trying to determine if text is < 40 was posing the problem.

So i think an automated clean up procedure as you suggested is the way forward

Regards

J.

 

[SkipVought]

assuming that you have a cell NAMED One with a value of 1...

Sub ReenterCells()
  [One].copy
  'select the column to convert
  intersect(Activesheet.UsedRange, Selection).PasteSpecial xlPasteAll, Operation:=xlMultiply
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}