Find Exact Value in Formula

[CarolynKo]

I need to search my Excel formulas to find 100000 (5 0s) but do not want to find 1000000 (6 0s). Using VBA or built-in Excel Find function takes me to every occurrence of 1000000 (6 0s). Entering 100000 (5 0s) - it's treated as if there is a wildcard, which normally works well, but not in this case.

Any pointers would be appreciated.

Carolyn

 

[PHV]

Even then you say LookAt:=xlWhole ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Carolyn,

Are these REALLY numbers OR are they STRINGS of NUMERIC DIGITS -- it amkes a BIG difference with SORT & FILTER!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CarolynKo]

PH: Using xlWhole produces the same result.

I'll restate the situation for anyone else who might have a solution: In my formulas, I want to Find (and possibly Replace) 100000 (5 0s) with 1000000 (6 0s). When I invoke Find I do not want to be taken to 1000000 (6 0s).

 

[Tiglet]

Hi,

Haven't got Excel on this machine so hope syntax is right.

Could you have another column (B) with a formula of
=IF(len(a1)=6,a1,"") (assuming your data is in column a) You can then do a search on column B.

To merge columns back together again do (in col c)

=If(b1="",a1,b1)

This is assuming you only have one or two columns of data. It's perhaps not the cleanest solution but it will do what you want

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[foundryqa]

Carolyn,

This seemed to do the trick for me:

Public Sub SeekandReplace()
Dim x As String
Dim y As String
'Have some formula in cell a2 like =2*100000
x = Range("a2").Formula
y = Replace(x, "100000", "1000000")
Range("b2").Formula = y
End Sub

This code could easily be adapted for a range of cells or an if statement. It should get you started though. If you need any more help, give a shout.

Fred

 

[CarolynKo]

Fred, thanks for the suggestion. Unfortunately it also finds 1,000,000 and makes it 10,000,000. In essence I need something that works like:

find formula where equals (or contains) 100,000 AND not equal to (or does not contain) 1,000,000

Example of formula:
=(C11/(sheetoffset(-1, C$74)/4))*1000000

The sheet runs from A1:X84 and there are 13 sheets in the workbook which is why I am not checking manually.

Because the file has been worked on by many people and human error in general, I thought it would be good to check that in fact the value is 1 million (6 0s). So I want to check for 5 0s, 7 0s... not perfect but would find most common errors (one 0 too many or too little).

Need to search the sheet to find any formula/cell that has 100,000 (but not 1,000,000) and then I can manually fix it. There are probably few, if any, errors.

Thanks again,
Carolyn

 

[AdrianScaife]

How about using find as you have been and then checking the starting position of the "100000" using InSrt.

You can then check that the next character is not a "0" easily enough

 

[foundryqa]

Carolyn,

You were correct. Using Adrian's advice, I think this will do. I tested it more than my original submission.

Public Sub SeekandReplace()
Dim x As String
Dim y As Variant
'Have some formula in cell a2 like =2*100000
x = Range("a3").Formula
y = InStr(1, x, "100000")
If y = 0 Then
MsgBox ("It's smaller than 100,000")
Exit Sub
End If
Select Case Mid(x, y + 6, 1)
Case "0"
MsgBox ("It's bigger than 100,000")
Case ""
MsgBox ("It's 100,000")
End Select

End Sub

 

[CarolynKo]

I'm still playing around with this. Wanted to thank everyone for taking the time to respond. Learned some too about different options/features for use now and/or in the future.

 

[KenWright]

One way:-

Sub FindReplace()

Dim i As Long
Dim k As Integer
Dim num As Long
Dim find As Long
Dim rep As Long

findme = InputBox("What do you want to find")
repwith = InputBox("What do you want to replace " & findme & " with?")

i = Application.WorksheetFunction.CountIf(ActiveSheet.UsedRange, findme)
If i = 0 Then
MsgBox "There are no instances of that number in your data"
Exit Sub
End If

Cells.find(What:=findme, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, MatchCase:=False).Activate
k = Application.WorksheetFunction.find(ans, ActiveCell)

On Error Resume Next
For num = 1 To i
With ActiveCell
.Value = repwith
End With
Cells.FindNext(After:=ActiveCell).Activate
Next num

End Sub

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------