Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve variant array of cells as formatted in the cell
I often use code like the following to grab a variant array of data
containing cell data. Sub MySub dim rng as Range, vnt as Variant set rng=range("A1") vnt=rng end Sub However, this does not grab the data as they are seen in the cell (the formatted text of the cell. Is there any way to grab that data without having to use a function like the following? If not, using this function is acceptable - i just want to know for my own knowledge. Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant Dim i As Long, j As Long If rng Is Nothing Then Exit Sub ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To rng.Columns.Count) For i = LBound(GetCellsAsFormatted, 1) To UBound(GetCellsAsFormatted, 1) For j = LBound(GetCellsAsFormatted, 2) To UBound(GetCellsAsFormatted, 2) GetCellsAsFormatted(i, j) = Format(rng(i, j).Value, rng(i, j).NumberFormat) Next j Next i End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve variant array of cells as formatted in the cell
No, you can't do it in your usual fashion.
You can simplify your function by using the Text property. Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant Dim i As Long, j As Long If rng Is Nothing Then Exit Sub ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To _ rng.Columns.Count) For i = LBound(GetCellsAsFormatted, 1) To UBound(GetCellsAsFormatted, 1) For j = LBound(GetCellsAsFormatted, 2) To _ UBound(GetCellsAsFormatted, 2) GetCellsAsFormatted(i, j) = rng(i,j).Text Next j Next i End Function -- regards, Tom Ogilvy "R Avery" wrote in message ... I often use code like the following to grab a variant array of data containing cell data. Sub MySub dim rng as Range, vnt as Variant set rng=range("A1") vnt=rng end Sub However, this does not grab the data as they are seen in the cell (the formatted text of the cell. Is there any way to grab that data without having to use a function like the following? If not, using this function is acceptable - i just want to know for my own knowledge. Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant Dim i As Long, j As Long If rng Is Nothing Then Exit Sub ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To rng.Columns.Count) For i = LBound(GetCellsAsFormatted, 1) To UBound(GetCellsAsFormatted, 1) For j = LBound(GetCellsAsFormatted, 2) To UBound(GetCellsAsFormatted, 2) GetCellsAsFormatted(i, j) = Format(rng(i, j).Value, rng(i, j).NumberFormat) Next j Next i End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieve variant array of cells as formatted in the cell
excellent. The Text property always returns the value as formatted in
the cell. I was looking for that. Thanks. Tom Ogilvy wrote: No, you can't do it in your usual fashion. You can simplify your function by using the Text property. Public Function GetCellsAsFormatted(rng As Excel.Range) As Variant Dim i As Long, j As Long If rng Is Nothing Then Exit Sub ReDim GetCellsAsFormatted(1 To rng.Rows.Count, 1 To _ rng.Columns.Count) For i = LBound(GetCellsAsFormatted, 1) To UBound(GetCellsAsFormatted, 1) For j = LBound(GetCellsAsFormatted, 2) To _ UBound(GetCellsAsFormatted, 2) GetCellsAsFormatted(i, j) = rng(i,j).Text Next j Next i End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions | |||
Variant Array | Excel Programming | |||
Best way to paste a variant array into a range? | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming | |||
Reading formatted cell values into an array | Excel Programming |