Need Help w/ VLookup Formatting

M

Mike Lindsay

How do I write a macro or a formula that allows me to not just copy the data
in V-Lookup function but the way the data is formatted as well. My Sample
formula is as follows: =VLOOKUP(B2,Sheet2!A1:C4,2). Is there a way to
re-write this formula to copy the formatting as well or possible a macro can
be written?

Appreciations to anyone who can answer this question .
 
D

Dave Peterson

Formulas return values--not formatting.

Maybe you could use some worksheet event that copies and pastes the
cell--depending on how B2 is changed.
 
M

Mike Lindsay

Ok, ignoring the formula is there a way to set up the sheet to copy the
formatting. You provided excellent assistance on how to have the cells
automatically size themselves to the text being copied, can something similar
be done using the "view code" option for the sheet in question to have the
data copied exactly as it is in the data range?
 
R

Richard Buttrey

How do I write a macro or a formula that allows me to not just copy the data
in V-Lookup function but the way the data is formatted as well. My Sample
formula is as follows: =VLOOKUP(B2,Sheet2!A1:C4,2). Is there a way to
re-write this formula to copy the formatting as well or possible a macro can
be written?

Appreciations to anyone who can answer this question .
Click to expand...


I suppose it depends on how many of these formulae you have.

As a general example assuming just one Vlookup in cell A1which is
named "MyVlookup", and with the value that is a result of the look up
in column C of a table, you could put the following in a VBA
procedure.

Sub TestCopyFormat()
Columns("C:C").Find(what:=Range("A1").Text, after:=Range("C1")).Copy
Range("MyVlookup").PasteSpecial (xlPasteFormats)
End Sub

If there are several vlookups you could no doubt adapt this and build
in a looping procedure.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
M

Mike Lindsay

Richard Thank You for the suggestion but it does not appear to work. I set
up a spread sheet as you used in your example. W/ the VLookup in cell "A1"
and the data table encompasing information in Column "C". I re-named cell
A1 "MY VLookup". I put the code you sent into the VBA, but while I did not
recieve an erro message the Cell w/ the VLookup formula (A1) did not change
formatting to match the information in cell C1. Any further suggestions?
 
R

Richard Buttrey

OK let's double check.

A1: =VLOOKUP(2,B:C,2)
B1: 1
B2: 2
B3: 3
C1: Test1
C2: Test2
C3 Test3

Now format C2 with some distinctive pattern and/or font and run the
macro. In my test workbook it changes A1 to the same format as C2.

Post back if you're still having probs.

Rgds


Richard Thank You for the suggestion but it does not appear to work. I set
up a spread sheet as you used in your example. W/ the VLookup in cell "A1"
and the data table encompasing information in Column "C". I re-named cell
A1 "MY VLookup". I put the code you sent into the VBA, but while I did not
recieve an erro message the Cell w/ the VLookup formula (A1) did not change
formatting to match the information in cell C1. Any further suggestions?
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
M

Mike Lindsay

Richard,
I did exactly as you suggested and I cut & Pasted the follow into the Visual
Basic Editor:

Sub TestCopyFormat()
Columns("C:C").Find(what:=Range("A1").Text, after:=Range("C1")).Copy
Range("MyVlookup").PasteSpecial (xlPasteFormats)
End Sub

It still did not change the formatting in A1. Would you object to e-mailing
me the sample spread you created?
 
M

Mike Lindsay

I also re-named "A1" " MyVlookup" and that did not make a difference either.
I appreciate the time you have commited to my problem.

Thanks A lot
 
M

Mike Lindsay

Richard,
My email address is "[email protected]" I appreciate all you
have done to assist me. Hopefully view your spread sheet and visual basics
the run in the backround will solve my problem. I will keep you posted.

Thank You.
 
Top