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 .

Formulas return values--not formatting.

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

Mike Lindsay wrote:

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

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?

"Dave Peterson" wrote:

Formulas return values--not formatting.

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

Mike Lindsay wrote:

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

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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?

"Richard Buttrey" wrote:

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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


"Mike Lindsay" wrote:

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?

"Richard Buttrey" wrote:

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 4 May 2006 12:44:03 -0700, Mike Lindsay
wrote:

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" wrote:

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 4 May 2006 12:44:03 -0700, Mike Lindsay
wrote:

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




"Richard Buttrey" wrote:

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard,
My email address is " 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.

"Richard Buttrey" wrote:

On Thu, 4 May 2006 12:44:03 -0700, Mike Lindsay
wrote:

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




"Richard Buttrey" wrote:

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


On Thu, 4 May 2006 11:13:01 -0700, Mike Lindsay
wrote:

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" wrote:

On Thu, 4 May 2006 06:33:01 -0700, Mike Lindsay
wrote:

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
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________