Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace Vlookups with Values (VBA Excel)

Status
Not open for further replies.

miwoodar

Technical User
Dec 4, 2006
34
US
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
 
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
 
Perfect! Thanks so much.

____________________
Mike
 
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
 
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
 
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
 
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
 
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
 
I meant, press the F1 key with the cursor inside the InStr word.
 
LOL...Can you tell I'm new to VBA?

____________________
Mike
 
morning all,
simple issue that escapes me at the moment...
Code:
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
 
Longhair - Was this supposed to be a new thread?

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
miwoodar,
caught that this am. thought i posted a sorry and then opened a new thread.
sorry for the confusion.
regards,
longhair
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top