Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve cell value & format at the same time
Im looping thru a range using two variables that will transfer the outcome
to another sheet. I want to retrieve the cell value and the font format so it carries any bolds. (No paste special...) Variables-loop 1 NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3) StreetLEG = Sheet1.Cells(RowLEG, 4) Outcome-loop2 Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value = StreetLEG Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve cell value & format at the same time
The "Font" class is read-only for the Range, so something like this
doesn't work: Set ar = ActiveCell.Font Set rng = ActiveCell.Offset(3, 0) rng.Value = ActiveCell.Value Set rng.Font = ar There are about 10-15 properties of the Font class that you could set individually, something like this: Set ar = ActiveCell.Font Set rng = ActiveCell.Offset(3, 0) rng.Value = ActiveCell.Value rng.Font.Background = ar.Background rng.Font.Color = ar.Color rng.Font.ColorIndex = ar.ColorIndex rng.Font.FontStyle = ar.FontStyle rng.Font.Italic = ar.Italic (and so on, or maybe there are only a few values that you care about, such as only using Bold) Or you could use the copy and pastespecial(all) methods like this: Set rng = ActiveCell.Offset(3, 0) ActiveCell.Copy rng.PasteSpecial xlPasteAll Application.CutCopyMode = False The downside here would be that you couldn't store the values to be copied all at once, then paste, you'd have to copy-paste, copy-paste, copy-paste (which may be what you're doing anyway). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve cell value & format at the same time
NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3)
NameLEGBold = Sheet1.Cells(RowLEG, 2).Font.Bold Or Sheet1.Cells(RowLEG, 3).Font.Bold StreetLEG = Sheet1.Cells(RowLEG, 4) StreetLEGBold = Sheet1.Cells(RowLEG, 4).Font.Bold -- Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Font.Bold = NameLEGBold Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value = StreetLEG Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Font.Bold = StreetLEGBold "LuisE" wrote in message ... | Im looping thru a range using two variables that will transfer the outcome | to another sheet. I want to retrieve the cell value and the font format so it | carries any bolds. (No paste special...) | | Variables-loop 1 | | NameLEG = Sheet1.Cells(RowLEG, 2) & " " & Sheet1.Cells(RowLEG, 3) | StreetLEG = Sheet1.Cells(RowLEG, 4) | | | | Outcome-loop2 | Worksheets("Labels").Cells(LastRowLabel, LastColLabel).Value = NameLEG | Worksheets("Labels").Cells(LastRowLabel + 1, LastColLabel).Value = StreetLEG | | Thanks in advance | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
cell format for date/time in same cell excel 2003 | Excel Worksheet Functions | |||
Can I retrieve a value and its format using the minimum function? | Excel Worksheet Functions | |||
Retrieve and update 1 record at a time | Excel Programming | |||
Test a file to retrieve format | Excel Programming |