How to force an alert to default NO?

[phrankndonna]

Hi,

Before I run this VBA code, I select a range of cells in a table where numerical data has been copied from another source. As a result of the copy, there is always a blank space before the values start. I want the macro to go through JUST the selected cells and remove the leading blank space.

So, I have a Find/Replace routine where it cycles through each number to Find [blank]digit (e.g., _0) and replace it with just the digit (e.g., 0). After it cycles through each digit in the range of cells, I get an alert telling me how many instances were replaced and asking if I want to check the whole document. Outside of the routine, my answer is no. But, if I use the code to turn off the display of the alerts, the default answer appears to be Yes. How do I turn off the alert display and yet force the answer to be No?

Thanks.

Frank

[Paul_Hossler]

So, I have a Find/Replace routine where it cycles through each number to Find [blank]digit (e.g., _0) and replace it with just the digit (e.g., 0). After it cycles through each digit in the range of cells,


Can we see it?

There's a lot of options on .Find. This fragment seems to replace and then stop


Sub Macro1()
With Selection.Find
.Text = "( )([0-9])"
.Replacement.Text = "\2"
.Forward = True
.Wrap = wdFindStop '<===========
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub


Paul

[gmaxey]

Or abandon Find and explicitly check for and delete spaces:

Sub ScratchMacro()
Dim oCell As Word.Cell
For Each oCell In Selection.Cells
Do While oCell.Range.Characters.First = Chr(32)
oCell.Range.Characters.First.Delete
Loop
Next oCell
End Sub

[phrankndonna]

Or abandon Find and explicitly check for and delete spaces:

Sub ScratchMacro()
Dim oCell As Word.Cell
For Each oCell In Selection.Cells
Do While oCell.Range.Characters.First = Chr(32)
oCell.Range.Characters.First.Delete
Loop
Next oCell
End Sub

This was absolutely perfect! Especially in comparison with my approach, which was to use Find/Replace for each digit 0-9 (excerpt below). Thank you VERY much for your help!

With Selection.Find
.Text = " 1"
.Replacement.Text = "1"
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Frank

[macropod]

Even faster, especially if you're not worried about the text formatting and you want to clear out any spaces at the end of each cell, is:
Sub Demo()
Application.ScreenUpdating = False
Dim oCell As Cell, Rng As Range
With Selection
If .Information(wdWithInTable) = False Then
MsgBox "Please select one table", vbCritical, "No table!"
GoTo Done
Else
For Each oCell In .Cells
Set Rng = oCell.Range
Rng.End = Rng.End - 1
oCell.Range.Text = Trim(Rng.Text)
Next oCell
End If
End With
Set Rng = Nothing
Done:
Application.ScreenUpdating = True
End SubNote the added error-checking, etc.

[Paul_Hossler]

Greg & Paul ---

You both can up with 'looping' solutions that work, and Frank's got an answer, so life is good.

However, I read Frank's question



.... through each number to Find [blank]digit (e.g., _0) and replace it with just the digit (e.g., 0).


maybe too literally and only checked for " 1", " 2", etc. to replace, but if it's always numerical data in the selection, it's the same thing.

Question: Is the looping approach performance-wise pretty much a wash? In general, I try to avoid writting my own loops if Word has something built-in

Paul

[macropod]

Hi Paul,

If one takes the OP's problem description literally, then your's is a fine solution. It's limitation, though, is that it'll remove spaces before other numbers too if there's more than one such string in a cell. And, given that some regions use spaces as thousands separators, that's entirely possible. Further, your solution only removes a single preceding space, whereas Greg's and mine remove multiple preceding spaces. That said, your's could clean up multiple preceding spaces too, by changing:
.Text = "( )([0-9])"
to:
.Text = "([ ]{1,})([0-9])"

Performance-wise, Word's Find/Replace function will undoubtedly beat any vba looping routine, simply because it's a native function, not because of any looping, per se.

[Paul_Hossler]

Ahhh



though, is that it'll remove spaces before other numbers too if there's more than one such string in a cell.



I tried to use .Text = "<( )([0-9])" but couldn't get it to work

Thanks, Prof. Edstein :clever:

Paul