Question about keeping cell properties when doing a Vlookup

M

macklo54

Board Regular
Joined
May 26, 2004
Messages
69
I have a data sheet that I want to run a VLookup on. However, each cell on that data sheet has unique properties, such as bolded words or different sized fonts. When I complete the Vlookup these properties are lost.

Is there any way to keep these properties after completing a Vlookup???
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Re: Question about keeping cell properties when doing a Vloo

Not with standard functions. You could probably accomplish it with VBA by searching for the match, then copying it and pasting it back to the destination.
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

Nope. Can't even do it with a simple B1 =A1, much less via VLookup(). Only way to preserve that sort of thing is copy and paste (or a VBA macro that would essentially be doing the same thing).
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

Hi macklo54, as the others have stated you cant grab the format as well with the standard lookup. However, the following macro should be able to do this for you.

Right click the sheet concerned and select View Code then paste into the right hand window. This macro will run whenever the sheet calculates.

Code: 
Private Sub Worksheet_Calculate()
Dim Vpos As Integer, FmlArr() As String, TgtRng As Range, cell As Range, c

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.HasFormula = True Then
Vpos = InStr(1, cell.Formula, "VLOOKUP", vbTextCompare)

If Vpos > 0 Then
FmlArr = Split(Mid(cell.Formula, Vpos, Len(cell.Formula) - Vpos + 1), ",", -1, vbTextCompare)
Set TgtRng = Range(FmlArr(1))
Set TgtRng = Intersect(TgtRng, Columns(TgtRng(, Val(FmlArr(2))).Column))

With TgtRng
    On Error Resume Next
    Set c = .Find(cell, LookIn:=xlValues)
        If Not c Is Nothing Then
        c.Copy
        cell.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        End If
    Error 0
End With
End If
End If
Next cell
End Sub

hth


EDIT: Note that the Vlookup formula should be entered as follows with the last argument of true/false included....
=VLOOKUP(A3,$G$1:$H$5,2,FALSE)
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

I tried copying and pasting but I get a debug error for this line

Set TgtRng = Range(FmlArr(1))

any clue?
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

Hi, I just placed an edit in my post. You must enter the vlookup including the last argument of false/true. The Vlookup can be encompassed in another formula such as IF but the vlookup part needs that last argument to be present.
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

Hi Parry that's a heck of a terrific try - but I think the OP is asking to copy formats on Characters within the cells. In my test your code copied the format from the first character of the looked-up cell and applied to all characters in the cell with the VLookup formula. If you figure out how to format specific characters with a formula's output, I will be very, very, very impressed!
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

Hi Greg, thats a fair bit harder. :)

The cell format is being copied, but if individual letters were bolded such as "yellow" then these are lost. I don't think this is achievable and still retain the formula in the cell.

If this is the case then I would forget about trying to use code to adjust the result of the formula and instead use code to do the whole thing and not have a formula at all. I only did it because I like a challenge - its not usually a good idea to try and determine the result of the formula in this way.

If its still not working for you macklo54 could you please give an example of the formula used and the expected result (ie are individual letters bolded or are you talking about the whole cells format as I have done).
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

I am talking about the whole cell format, such as the boarders, font, size, text, etc...

I tried using the code you gave but it still does not work.

Maybe I am doing something wrong?

Thanks for the help!
 
Upvote 0
Re: Question about keeping cell properties when doing a Vloo

As I said, you will need to provide an example of your formula plus expected results. Without it its impossibe to help you any further.

The line Set TgtRng = Range(FmlArr(1)) may fail if the lookup range is a different book or sheet. Is this the case? It's more work to cover all combinations so I made the presumption everything was in the same sheet.

Presuming the lookup range is in a different sheet but in the same workbook heres an amendment which will hopefully work.

Code: 
Private Sub Worksheet_Calculate()
Dim Vpos As Integer, FmlArr() As String, TgtRng As Range, cell As Range, c
Dim ShArr() As String, Sh As Worksheet, Shpos As Integer

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If cell.HasFormula = True Then
Vpos = InStr(1, cell.Formula, "VLOOKUP", vbTextCompare)

If Vpos > 0 Then
FmlArr = Split(Mid(cell.Formula, Vpos, Len(cell.Formula) - Vpos + 1), ",", -1, vbTextCompare)
Shpos = InStr(1, FmlArr(1), "!", vbTextCompare)
If Shpos > 0 Then
ShArr = Split(FmlArr(1), "!", -1, vbTextCompare)
Set Sh = Worksheets(ShArr(0))
Set TgtRng = Sh.Range(ShArr(1))
Else
Set Sh = ActiveSheet
Set TgtRng = Sh.Range(FmlArr(1))
End If


Set TgtRng = Intersect(TgtRng, Sh.Columns(TgtRng(, Val(FmlArr(2))).Column))

With TgtRng
    On Error Resume Next
    Set c = .Find(cell, LookIn:=xlValues)
        If Not c Is Nothing Then
        c.Copy
        cell.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
        End If
    Error 0
End With
End If
End If
Next cell
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Another VLOOKUP Question
Replies
4
Views
301
LogPlanner
L
I
  • Question
Resize property when transferring data over
2
Replies
17
Views
267
ItalianPlatinum
I
L
  • Question
VLOOKUP Help
Replies
3
Views
298
LogPlanner
L
R
  • Question
Shape objects distorting on Mac only (not Windows)
Replies
0
Views
150
rline101
R
M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
374
MountainFog
M

Forum statistics

Threads
1,215,869
Messages
6,127,414
Members
449,382
Latest member
DonnaRisso

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top