Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108466 > unrolled thread

Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric

Back to article view | Back to microsoft.public.excel.programming

Contents

  Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric goaljohnbill <jbeary74@gmail.com> - 2016-01-26 15:28 -0800
    Re: Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric GS <gs@v.invalid> - 2016-01-26 22:18 -0500
    Re: Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric Claus Busch <claus_busch@t-online.de> - 2016-01-27 07:23 +0100

#108466 — Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric

So I have this code to replace an original value with a user entered one;


                      ' dim as variant so that if = false works on both cases
            Dim varReplaceValue As Variant
1        varReplaceValue = Application.InputBox(Prompt:="The replace prompt ", _
                                          Title:="the title", Type:=2)
                      ' if cancel input exit sub to not run next IPB
2           If varReplaceValue = False Then
3             MsgBox ("Replace input canceled, process terminating")
4               GoTo CleanAndExit
5           Else
              ' nothing 
6          End If
                      ' dim as variant so that if = false works on both cases
            Dim varFillValue As Variant
7         varFillValue = Application.InputBox(Prompt:="The fill prompt ", _
                                          Title:="the title", Type:=2)
8           If varFillValue = False Then
9             MsgBox ("Fill input canceled, process terminating")
10              GoTo CleanAndExit
11          Else
                Dim rngSelection As Range
12             Set rngSelection = Selection
                Dim rngCell As Range
13              For Each rngCell In rngSelection
14                  If rngCell.Value = varReplaceValue Then
15                    rngCell.Value = varFillValue
16                 End If
              ' do other things to rngCell
17             Next rngCell
18              Set rngSelection = Nothing
19         End If


It was originally for replacing text with numbers greater than 0 in a selected range and it worked great. Now the values I want to use it for are causing problems. if the varReplaceValue (line1) is numeric it has quotes around it and doesnt match to numeric values in the ForEach else (lines 13-17). If the varFillValue (Line7) is 0 it has quotes and is treated as false and runs the exit message (Lines 9-10). 

What I need is a check for Variant values that will make a number numeric including 0 and not do anything at all to text. I couldnt find a solution that did both in a relatively short format that I could follow.

Thank you in advance for consideration

gjb

[toc] | [next] | [standalone]

#108467

Did you look into using the IsNumeric() function?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108468

Hi,

Am Tue, 26 Jan 2016 15:28:53 -0800 (PST) schrieb goaljohnbill:

>                 Dim rngSelection As Range
> 12             Set rngSelection = Selection
>                 Dim rngCell As Range
> 13              For Each rngCell In rngSelection
> 14                  If rngCell.Value = varReplaceValue Then
> 15                    rngCell.Value = varFillValue

if you use Range.Replace instead comparing each cell  it works with
numbers and text:

Dim rngSelection As Range
Set rngSelection = Selection
rngSelection.Replace varReplaceValue, varFillValue


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web