Removing Decimal Points In An Array- VBA HELP

[milwphil]

Collective Mr. Excel Brain,

I'm trying to come up with code to remove decimals points in a couple columns of a spreadsheet.

The spreadsheet that I inherited uses dataArray's... What I'd like to do is somehow remove the decimal points in a couple of columns (M and N).

I thought using a similar method to the below method would work (after the number has been rounded), but I'm not having much luck.

[COLOR=red][SIZE=3][FONT=Calibri]If Val(dataArray(tmpRow, tmpCol)) <> Val(Round(Val(dataArray(tmpRow, tmpCol)), 0)) Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Calibri]                  cellWarning = True<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Calibri]                  Call updateWarnText(tmpRow, tmpCol, "The location # was changed from " & dataArray(tmpRow, tmpCol) & " to " & Round(Val(dataArray(tmpRow, tmpCol)), 0))<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Calibri]                  dataSheet.Cells(tmpRow, tmpCol).Value = Val(Round(Val(dataArray(tmpRow, tmpCol)), 0))<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=red][SIZE=3][FONT=Calibri]              End If[/FONT][/SIZE][/COLOR]<o:p></o:p>

Any suggestions?

YOUR HELP IS GREATLY APPRECIATED!

 

[Scott Huish]

Why not just use Replace?

Sub test()
Range("M:N").Replace ".", ""
End Sub

 

[Ruddles]

I'm unclear what "remove decimals points" means. 123.45 -> 123? 678.9 -> 6789?

Can you give us some typical values and what you want them turned into?

 

[milwphil]

Thanks for the responses!

For example:

123.45 should be rounded to 123

However, when people cut and paste data from a table that's formatted as a number, into my spreadsheet it maintains the decimal points after rounding.

Subsequently, 123.45 is rounded to 123.00

I just want to remove the decimal places.

Is there any way I could add to my code after the rounding is completed to just remove the decimal places?

Thanks,
Phil

 

[GlennUK]

Change the number format of the area as you wish.

 

[milwphil]

GlennUK,

I'm not sure I understand your response. Where would I make the change.

The formats that are being cut and pasted are being transferred to my spreadsheet. Is there anyway to keep the format of my spreadsheet (in the code that I'm using to Round/warn the user of the rounding and also make the formatting seemless to the user)?

I apologize I'm still somewhat of a novice (it may very well be that the answer is so simple I'm making it difficult).

Thanks,
Phil

 

[GlennUK]

dataSheet.Cells(tmpRow, tmpCol).NumberFormat = "General"

 

[milwphil]

SIR, YOU ARE A GENIUS!!!!!!!!!! Works perfectly!

Thank you to everyone that responded.

Phil