Haystack & needle

[Csibész]

Hi...

I don't get it... perhaps somebody could explain this to me.

The macro below is to find a given value in a range. If I use the actual value in Find, there's no problem. If I try to feed same through a variable (preferred way), it bombs out.

Sooo... Where do I go wrong and more importantly, how to do this right? Thanx in advance for any enlightenment!

Regards...

Sub Macro3()
TBFound = Range("A1").Value ' say it is =50
Sheets("1").Range("b1:b10").Find(TBFound, xlValue).Select
' using 50 instead of TBFound in Find - works
' using TBFound gives error
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TBFound
End Sub

 

[KenCriss]

It's kind of hard to figure out where you are going with this. Can you tell us what you want the output to be?

 

[Seti]

Are you sure that A1 is a numeric value of 50 and not a text value?

 

[Datsmart]

Change:
Sheets("1").Range("b1:b10").Find(TBFound, xlValue).Select
to
Sheets("1").Range("b1:b10").Find(What:=TBFound).Select

Works for me.

 

[Joe Was]

To use a Var you need this syntax:


Dim Found As Range, myText$, FirstAddress$

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address

Do

'your code to do whatever here!

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If

 

[Csibész]

Seti: it is a numeric value...

KenCriss: I am looking-up a control value (vector) in a frequently updated column 2 that returns the corresponding value from col 3. This far I'm OK. Now, I need to find this returned value and put it in the same row but next column. This works with a constant but not with a variable.

Datsmart: Works all right up to 8 decimals, but not beyond (eg: 95.50561797 is OK, 95.505617971 bombs out). As luck has it, I have to accomodate up to 14 decimals... In Fortran I'd specify double precision to fix this. Do you have a similar trick for Excel?

Thanx guys!

 

[Seti]

Does adding:

DIM TBFound as Double

at the beginning of your code help?