UtterAccess Discussion Forums > Creating an Add-in

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

dashiellx2000

Oct 3 2007, 02:30 PM

I export lots of data from a web based program into Excel. Alot of the cells end up being empty, holding a single space or double spaces which throws off calculations, etc... So I want to create a add in to set all of these to zeros. Here is what I came up with, but it doesn't appear to be doing anything.

CODE

Sub SettoZero()
Dim strRange As String
Dim endRange As String
Dim totRange As String
Dim objSheet As Worksheet

strRange = InputBox("Enter Range Start", "Range")
endRange = InputBox("Enter Range End", "Range")
totRange = strRange & ":" & endRange

Set objSheet = Excel.ActiveSheet

objSheet.Range(totRange).Replace Null, 0
objSheet.Range(totRange).Replace "", 0
objSheet.Range(totRange).Replace " ", 0
objSheet.Range(totRange).Replace " ", 0

End Sub

Can anyone tell me what I'm doing incorrectly?

Thanks.

NateO

Oct 3 2007, 03:22 PM

Excel can't have Nulls, and make sure those spaces aren't Chr(160), a non-breaking space. Not the same as Chr(32) and very popular in Web-based programming for some reason.

In that case, you might want something like the following:

http://www.mrexcel.com/board2/viewtopic.php?p=417035#417035

dashiellx2000

Oct 4 2007, 07:37 AM

Thanks, Nate. That got it working. Here is the final I came up with:

CODE

Sub SettoZero()
Dim strRange As String
Dim endRange As String
Dim totRange As String
Dim objSheet As Worksheet

strRange = InputBox("Enter Range Start", "Range")
endRange = InputBox("Enter Range End", "Range")
totRange = strRange & ":" & endRange

Set objSheet = Excel.ActiveSheet

objSheet.Range(totRange).Replace Chr(160), 0
objSheet.Range(totRange).Replace Chr(32), 0
objSheet.Range(totRange).Replace "", 0
objSheet.Range(totRange).Replace " ", 0
objSheet.Range(totRange).Replace " ", 0

End Sub

It is probably a bit redundant, but I want to account for all the possibilities.

Thanks again for all your help as of late.

NateO

Oct 4 2007, 12:55 PM

Hi William,

Chr(32) and " " should be the same, so you shouldn't have to do that twice.

One more thought would be to group your code with a With Statement, e.g.,

CODE

With objSheet.Range(totRange)
    .Replace Chr(160), 0
    .Replace vbNullString, 0
    .Replace " ", 0
    .Replace "  ", 0
End With

Glad to hear you're up and running. o!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.