adjust cell width change font.

P

Peter

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
 
J

JLGWhiz

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
 
F

FSt1

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
 
F

FSt1

hi
just notice something.
i would move the row.autofit to after the font.size .......just in case.

regards
FSt1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top