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!

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

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

Status
Not open for further replies.

naikadit

Technical User
Jun 30, 2008
22
US
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
 
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?
 
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top