| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adjust cell width change font.
Hello
I'm doing a copy and paste of cell values, my question is, when I paste the values into the cells, how do I get them to be all the same font, and some of the cells are text string, so how do I have te cells adjust for width? Currently I've got For Each x In Range("c1:c100") If Not IsEmpty(x) Then With SrcRange Set rngFind = .Find(z) If Not rngFind Is Nothing Then x.Offset(0, 7) = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value End If End With End If Next x and that gives me Note different font size... then the first cell needs t obe adjusted for the correct width. 5 Bay Base Station B 4/3/2009 220700 1/1/1900 12005 Some example Test B 1/28/2008 31100 11000 1/1/1900 10729 More Example text shown here A 1/27/2009 69374 2567 1/1/1900 11713 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adjust cell width change font.
Between your End If and End With you could add:
Columns(x.Offset(0, 7).Column).Autofit Range(x.Offset(0, 8).Address, _ x.Offset(0, 13).Address).Font.Size = 12 '<<or whatever size "Peter" wrote in message ... Hello I'm doing a copy and paste of cell values, my question is, when I paste the values into the cells, how do I get them to be all the same font, and some of the cells are text string, so how do I have te cells adjust for width? Currently I've got For Each x In Range("c1:c100") If Not IsEmpty(x) Then With SrcRange Set rngFind = .Find(z) If Not rngFind Is Nothing Then x.Offset(0, 7) = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value End If End With End If Next x and that gives me Note different font size... then the first cell needs t obe adjusted for the correct width. 5 Bay Base Station B 4/3/2009 220700 1/1/1900 12005 Some example Test B 1/28/2008 31100 11000 1/1/1900 10729 More Example text shown here A 1/27/2009 69374 2567 1/1/1900 11713 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adjust cell width change font.
hi
small techicallity. you're not coping and pasting. you are just having one range value equal another range value. copy and paste uses the windows clipboard. in this case we are not using the clipboard. you need to add code to adjust the width and set the fonts. i would use the resize method and do it as the end of each loop. this should work.... x.Offset(0, 7).Value = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value x.Offset(0, 7).Resize(1, 7).Columns.AutoFit ' adjust if needed x.EntireRow.AutoFit x.Offset(0, 7).Resize(1, 7).Font.Name = "Arial" x.Offset(0, 7).Resize(1, 7).Font.Size = 10 regards FSt1 "Peter" wrote: Hello I'm doing a copy and paste of cell values, my question is, when I paste the values into the cells, how do I get them to be all the same font, and some of the cells are text string, so how do I have te cells adjust for width? Currently I've got For Each x In Range("c1:c100") If Not IsEmpty(x) Then With SrcRange Set rngFind = .Find(z) If Not rngFind Is Nothing Then x.Offset(0, 7) = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value End If End With End If Next x and that gives me Note different font size... then the first cell needs t obe adjusted for the correct width. 5 Bay Base Station B 4/3/2009 220700 1/1/1900 12005 Some example Test B 1/28/2008 31100 11000 1/1/1900 10729 More Example text shown here A 1/27/2009 69374 2567 1/1/1900 11713 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adjust cell width change font.
hi
just notice something. i would move the row.autofit to after the font.size .......just in case. regards FSt1 "FSt1" wrote: hi small techicallity. you're not coping and pasting. you are just having one range value equal another range value. copy and paste uses the windows clipboard. in this case we are not using the clipboard. you need to add code to adjust the width and set the fonts. i would use the resize method and do it as the end of each loop. this should work.... x.Offset(0, 7).Value = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value x.Offset(0, 7).Resize(1, 7).Columns.AutoFit ' adjust if needed x.EntireRow.AutoFit x.Offset(0, 7).Resize(1, 7).Font.Name = "Arial" x.Offset(0, 7).Resize(1, 7).Font.Size = 10 regards FSt1 "Peter" wrote: Hello I'm doing a copy and paste of cell values, my question is, when I paste the values into the cells, how do I get them to be all the same font, and some of the cells are text string, so how do I have te cells adjust for width? Currently I've got For Each x In Range("c1:c100") If Not IsEmpty(x) Then With SrcRange Set rngFind = .Find(z) If Not rngFind Is Nothing Then x.Offset(0, 7) = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value End If End With End If Next x and that gives me Note different font size... then the first cell needs t obe adjusted for the correct width. 5 Bay Base Station B 4/3/2009 220700 1/1/1900 12005 Some example Test B 1/28/2008 31100 11000 1/1/1900 10729 More Example text shown here A 1/27/2009 69374 2567 1/1/1900 11713 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
adjust cell width change font.
Thanks for the help, that works
"Peter" wrote in message ... Hello I'm doing a copy and paste of cell values, my question is, when I paste the values into the cells, how do I get them to be all the same font, and some of the cells are text string, so how do I have te cells adjust for width? Currently I've got For Each x In Range("c1:c100") If Not IsEmpty(x) Then With SrcRange Set rngFind = .Find(z) If Not rngFind Is Nothing Then x.Offset(0, 7) = rngFind.Offset(0, 1).Value x.Offset(0, 8).Value = rngFind.Offset(0, 3).Value x.Offset(0, 9).Value = rngFind.Offset(0, 6).Value x.Offset(0, 10).Value = rngFind.Offset(0, 7).Value x.Offset(0, 11).Value = rngFind.Offset(0, 8).Value x.Offset(0, 12).Value = rngFind.Offset(0, 2).Value x.Offset(0, 13).Value = rngFind.Offset(0, 4).Value End If End With End If Next x and that gives me Note different font size... then the first cell needs t obe adjusted for the correct width. 5 Bay Base Station B 4/3/2009 220700 1/1/1900 12005 Some example Test B 1/28/2008 31100 11000 1/1/1900 10729 More Example text shown here A 1/27/2009 69374 2567 1/1/1900 11713 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell width auto adjust | Excel Discussion (Misc queries) | |||
| adjust width of single cell in Excel | Charts and Charting in Excel | |||
| How can I adjust column width of a single cell within Excel 2003? | Excel Discussion (Misc queries) | |||
| how to adjust the width of a cell and not the entire column | Excel Discussion (Misc queries) | |||
| Adjust Cell Width in Visual Basic | Excel Discussion (Misc queries) | |||
![Peter[_66_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
