Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing cells with text to cells with numeric values. David B Excel Programming 1 April 25th 08 08:05 PM
Need Input Box to only Accept Numeric Values [email protected] Excel Programming 6 February 3rd 07 10:26 PM
Comparing Times in Text Boxes to Times in Cells Matt[_39_] Excel Programming 1 August 6th 06 04:10 AM
Comparing Text to Numeric Vals DJS Excel Programming 2 September 8th 05 03:41 PM


All times are GMT +1. The time now is 09:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"