Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode
Hello. Is there a way to have vba scan an entire sheet (activesheet) and
hardcode any cells that have a VLookup formula in the cell? I was going to just hardcode the entire sheet, but i also have a lot of sum formulas that I can't have hardcoded. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode
One way:
Dim rCell As Range For Each rCell In ActiveSheet.UsedRange.Cells If UCase(Left(rCell.FormulaR1C1, 8)) = "=VLOOKUP" Then rCell.Value = rCell.Value Next rCell HTH "Steph" wrote: Hello. Is there a way to have vba scan an entire sheet (activesheet) and hardcode any cells that have a VLookup formula in the cell? I was going to just hardcode the entire sheet, but i also have a lot of sum formulas that I can't have hardcoded. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hardcode
Maybe...
Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range With ActiveSheet Set myRng = Nothing On Error Resume Next Set myRng = .Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No Formulas!" Exit Sub End If For Each myCell In myRng.Cells If myCell.Formula Like "*VLOOKUP(*" Then myCell.Value = myCell.Value End If Next myCell End With End Sub But it will get fooled with formulas like: =a1&"Steph, don't forget to include your =VLOOKUP() formula here" But that's probably not too likely????? Steph wrote: Hello. Is there a way to have vba scan an entire sheet (activesheet) and hardcode any cells that have a VLookup formula in the cell? I was going to just hardcode the entire sheet, but i also have a lot of sum formulas that I can't have hardcoded. Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Y Axis : Dynamically Hardcode Min/Max Values? | Charts and Charting in Excel | |||
hardcode a column in a formula | Excel Worksheet Functions | |||
Hardcode links - loop through sheets | Excel Programming | |||
Hardcode Links - edit my code please? | Excel Programming | |||
Change hardcode to ....? | Excel Programming |