Replace Vlookups with Values (VBA Excel)

[miwoodar]

I'm trying to find a fast way to locate all of my Vlookup formulas and replace them as values. Apparently I left my brain at home today. Can anyone please help?

Dim Rng As Range, ix As Long
Set Rng = Intersect(Range("a:a"), ActiveSheet.UsedRange)
For ix = Rng.count To 1 Step -1
If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "VLookup" Then 'truncates the value of cells, then makes the action specified below
Rng.Item(ix).Copy.pastevalues
End If
Next

Mike



____________________
Mike

 

[PHV]

Something like this ?
Dim Rng As Range
For Each Rng In Intersect(Range("A:A"), ActiveSheet.UsedRange)
If Rng.HasFormula Then
If InStr(1, Rng.Formula, "VLookup", 1) Then
Rng.Value = Rng.Value
End If
End If
Next

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

 

[miwoodar]

Perfect! Thanks so much.

____________________
Mike

 

[miwoodar]

Actually...one more quick question....

Is there a way to find multiple strings? For instance, what if I wanted to replace all formulas that have Lookup or Index/Match?

____________________
Mike

 

[PHV]

Have a look at the Or boolean operator.

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

 

[miwoodar]

Is this a fast way to write it?

For Each Rng In Intersect(Range("a:a"), ActiveSheet.UsedRange)
If Rng.HasFormula Then
If InStr(1, Rng.Formula, ("Vlookup" Or "Match"), 1) Then
Rng.Value = Rng.Value
End If
End If
Next

____________________
Mike

 

[PHV]

Seems you don't have a F1 key on your keyboard.
I'd try this instead:
If InStr(1, Rng.Formula, "Vlookup", 1) _
Or InStr(1, Rng.Formula, "Match", 1) Then

 

[miwoodar]

Ouch.
Here's what it said...it did not help...

"Boolean variables are stored as 16-bit (2-byte) numbers, but they can only be True or False. Boolean variables display as either True or False (when Print is used) or #TRUE# or #FALSE# (when Write # is used). Use the keywords True and False to assign one of the two states to Boolean variables.

When other numeric types are converted to Boolean values, 0 becomes False and all other values become True. When Boolean values are converted to other data types, False becomes 0 and True becomes -1."


____________________
Mike

 

[PHV]

I meant, press the F1 key with the cursor inside the InStr word.

 

[miwoodar]

LOL...Can you tell I'm new to VBA?

____________________
Mike

 

[longhair]

morning all,
simple issue that escapes me at the moment...

Dim counter As Integer
Do Until counter = 20
 counter = counter + 1
 astring = ActiveCell.Value
 bstring = ""
 If astring Like ("*B1M") Or astring Like ("*B5M") Then _
 bstring = Mid(astring, 1, (Len(astring) - 4))
 If astring Like ("*B100") Or astring Like ("*B500") Then _
 bstring = Mid(astring, 1, (Len(astring) - 5))
 If bstring > "" Then
  cstring = "=vlookup(" & bstring & ", Sheet1!last, 2, false)"
  ActiveCell.Offset(rowoffset:=0, columnoffset:=6).Activate
  ActiveCell.Value = cstring
  ActiveCell.Offset(rowoffset:=1, columnoffset:=-6).Activate
 Else: ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
 End If
Loop
[\code]
returns an application defined or object defined error on 'ActiveCell.Value = cstring'
i know i've used similar code before but cannot get the activecell to populate with cstring.
any suggestions?
thanks in advance.

regards,
longhair

 

[miwoodar]

Longhair - Was this supposed to be a new thread?

____________________
Mike

 

[PHV]

If bstring > "" Then
cstring = "=vlookup(""" & bstring & """, Sheet1!last, 2, false)"
ActiveCell.Offset(0, 6).Formula = cstring

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[longhair]

miwoodar,
caught that this am. thought i posted a sorry and then opened a new thread.
sorry for the confusion.
regards,
longhair