Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues comparing the values of 2 input boxes to cells and have eithervalue 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues comparing the values of 2 input boxes to cells and have either value be able to be text or numeric
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing cells with text to cells with numeric values. | Excel Programming | |||
Need Input Box to only Accept Numeric Values | Excel Programming | |||
Comparing Times in Text Boxes to Times in Cells | Excel Programming | |||
Comparing Text to Numeric Vals | Excel Programming |