Need Help w/ VLookup Formatting

[Guest]

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 .

 

[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.

 

[Guest]

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?

 

[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 .

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
__________________________

 

[Guest]

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?

 

[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?

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

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?

 

[Guest]

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

 

[Richard Buttrey]

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?

Yes, sure.

What email address? I tried the @discussions one but it bounces.

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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?

Yes, sure.

What email address? I tried the @discussions one but it bounces.

Rgds

I should have added that if you want to send me your address
privately, please send to

(e-mail address removed)


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Richard,
My email address is "(e-mail address removed)" 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.