UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Creating an Add-in    
 
   
dashiellx2000
post Oct 3 2007, 02:30 PM
Post #1

UtterAccess VIP
Posts: 9,209
From: Maryland
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.
Go to the top of the page
 
+
NateO
post Oct 3 2007, 03:22 PM
Post #2

Remembered
Posts: 5,055
From: Minneapolis, MN, USA
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
Go to the top of the page
 
+
dashiellx2000
post Oct 4 2007, 07:37 AM
Post #3

UtterAccess VIP
Posts: 9,209
From: Maryland
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.
Go to the top of the page
 
+
NateO
post Oct 4 2007, 12:55 PM
Post #4

Remembered
Posts: 5,055
From: Minneapolis, MN, USA
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!
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 01:34 PM