using replace to get the desired value?

[Keith_darcy]

Hello,
I'm using replace in VBA it looks like this:


VB:
Dim result As String
result = Replace(Range("d5"), "X", Range("f2"))
Range("k1") = result

If you like these VB formatting tags please consider sponsoring th
author in support of injured Royal Marines
This will look in d5 for X and replace it with f2. If instead of lookin
for X i want to look for the value in d4, how is it done? if i pu
range("d4") instead of X it doesn't work.

Thanks

 

[GS]

Try...

Dim sCriteria As String
sCriteria = Range("D4").Value
Range ("K1").Value = _
Replace(Range("D5").Value, sCriteria, Range("F2").Value)

--
Garry

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

 

[Ben McClave]

Keith,

Using Range("D4") in place of "X" worked fine for me. It could be that the value in D4 is not found within D5. Did you try it out by placing an "X" in cell D4?

When I tested it, your code was case-sensitive; so "x" is not the same as "X". You could use "UCase" to convert all of the Text to Upper-case and then use vbProperCase to return a string with only the first letter capitalized. Here is an example:

Dim result As String
result = Replace(UCase(Range("D5")), UCase(Range("D4")), UCase(Range("F2")))
Range("K1") = StrConv(result, vbProperCase)

Good luck sorting this out.

Ben