Excel - Vlookup Help

No. The 'strikethorugh' is just part of the text format and vlookups do not have anything to do with the format.

You could probably write a quick macro that checks if the format is strikethrough, if it is append an extra letter/digit to the end of the text and then it wouldn't be picked up by the vlookup as the cell contents wouldn't match the lookup value.
 
Here you go mate. This will append a '*' on to the end of any cells that have strike through applied to them

With your spreadsheet open, press ALT+F11 to open the VB editor. On the left there should be a tree window listing all of your open projects/spreadsheets. Right click on the spreadsheet you are working on and choose Insert > Module.
Expand the 'Modules' folder that should have appeared under your spreadsheet (in the tree window) and then double click on Module1.

Paste the code below into the window on the right.

You'll need to change the cells referenced in Range("A1:A100").Select line to whatever cells you are performing your vlookup on.

Code: 
Sub Identify_Strikethrough()

     Range("A1:A100").Select

     For Each Cell In Selection
          If Cell.Font.Strikethrough = True Then
               Cell.Value = Cell.Value & "*"
          End If
     Next Cell

End Sub

If you want to assign this macro to work from a button on your spreadsheet, go to View > Toolbars > Forms and then click the button control and place this where you want on your spreadsheet. If will then prompt you to choose the macro to assign to this button so just choose the one listed called Identify_Strikethrough.

Hope that helps!
 
Hi, it works thanks - is there anyway to have this always availble for use - ie current workbook selected ?
 
Aye, that can be done as well.

Need to head out just now so will post up the instructions when I get home later (unless someone else beats me to it).
 
Tools, Macro, Record new macro
Store Macro In Personal Macro Workbook

type something in a cell
stop recording

That will create a macro in Personal.xls, if Personal.xls does not exist then it will be created (and saved when you quit Excel).

Now, Tools, Macro, Macros select the macro then click Step into.
This opens the visual basic editor at the macro you have selected.
Rather than stepping through the code line by line just click the stop button.

Now you can copy and paste Meeko's code into the workbook and delete the macro you just created.

Assuming you want it to act on the currently selected cells then delete the line:
Range("A1:A100").Select
 
You must log in or register to reply here.
Back
Top Bottom