Want to remove all decimal places in the excel sheet using vbscript

[naikadit]

Hi guys,

I am a newbie in vbscript. The excel sheet which I am working on should not have decimal numbers so what I want to achieve is that if the script can show the cell numbers where there are decimal numbers and also if possible it can convert it into integers.

Prefreably I would be glad if I can get a code will show cells which has decimal numbers

I donno how to achieve this so I would be really glad if I can get any help in this

 

[NWBeaver]

Do you really need to use a VBScript, or could you do this from Excel using VBA? Are you able to put the code in Excel, or do you need an external application, such as a VB script?

 

[naikadit]

hi NWBeaver,

I want to use VB script itself coz I am halfway through the file and I cant go back now. actually what I want to achieve is that its hsould check range of colummns and should show a message that there is decimal numbers in teh cells.

 

[naikadit]

hi Nw,

I am trying something like this

Dim isFloat as boolean
Dim toTest as String
dim i as integer
dim k as integer
dim j as integer
For k=1 to 18
For j= 1 to 1000
toTest = cell(k,j).value
i=instr(1,toTest,".",vbTextCompare)
if i>0 then
isFloat=true
objtxtStream.Writeline "Decimal number in RBS Site "
Else
endif
Next j
Next i

 

[NWBeaver]

What part of the script are wanting help with?
It looks like you have a good idea of how to do this.

Here is a code sample which may help you.

'Search for decimal points in numeric values.
For Each objCell in objRange
If IsNumeric(objCell.Value) Then
i=InStr(1,objCell.value,".",vbTextCompare)
If i<>0 Then
'Highlight the cell and display the cell address
objCell.Interior.ColorIndex = 6
msgbox objCell.AddressLocal
End if
End If
Next

The code lines above will find decimal points in numeric values, highlight the cell and popup a message box with the cell address. You would need to define the objRange to make it work.