从多单元格范围获取格式化值 [英] Get formatted values from a multi-cell range
问题描述
Dim myText As String
myText = Range(a3)。Text
返回单元格A3中的格式化值,但
myText = Range(a3:c7 ).Text
给我一个错误。
如何从多单元格范围获取表示格式化值的字符串,同时保留数字格式?即输出文本的格式与从范围复制到文本编辑器的格式相同。
只有一个单个语句(无循环)才能将多个单元格值转换为数组的方法是使用Variant数组。
Dim varItemName作为变量
varItemName = Range(a3:c7)
如果你真的需要当您使用它们时,名称将键入 String
,然后只是 CStr
。
output = FunctionRequiringStringArgument(CStr(varItemName(1,2))
pre>
编辑:好的,你想要的是与表格格式相同的字符串。
这是一个完整的工作示例。
Dim strMyFormat1 As String
Dim varItemName As Variant
Dim strItemName()As String
Dim strItemNameBF()As String
Dim iCol As Long
Dim iRow As Long
Dim rngMyRange As范围
设置rngMyRange =范围(A3:C7)
varItemName = rngMyRange
ReDim strItemName(LBound(varItemName,1)To UBound(varItemName,1),_
LBound(varItemName,2)到UBound(varItemName,2))
'//获取格式的样本
strMyFormat1 = Range(A3)。NumberFormat
'//将格式示例应用于所有值
对于iRow = LBound(varItemName,1)到UBound(varItemName,1)
对于iCol = LBound(varItemName,2)到UBound (varItemName,2)
st rItemName(iRow,iCol)=格式(varItemName(iRow,iCol),strMyFormat1)
下一步iCol
下一步iRow
'//也可以仅应用于一些值 - 调整循环。
'//如果有多个格式样本,更多的循环会出现在这里。
'//如果所有单元格都有不同的格式,则必须使用强力 - 较慢。
ReDim strItemNameBF(1 to rngMyRange.Rows.Count,_
1 to rngMyRange.Columns.Count)
For iRow = 1 To rngMyRange.Rows.Count
对于iCol = 1到rngMyRange.Columns.Count
strItemNameBF(iRow,iCol)= rngMyRange.Cells(iRow,iCol).Text
下一步iCol
下一步iRow
Dim myText As String myText= Range("a3").Text
Returns the formatted value in cell A3, but
myText= Range("a3:c7").Text
gives me an error.
How do I get strings representing formatted values from a multi-cell range, while preserving the number format? i.e. the format of the output text would be the same as if copy-pasting from the range to a text editor.
解决方案The only way to get multiple cell values into an array with one single statement (no loops) is with a Variant array.
Dim varItemName As Variant varItemName = Range("a3:c7")
If you really absolutely need the names to be type
String
, then justCStr
them later when you use them.output = FunctionRequiringStringArgument(CStr(varItemName(1,2))
EDIT: Okay, okay, you want strings with same format as in sheet.
Here's a full working example.
Dim strMyFormat1 As String Dim varItemName As Variant Dim strItemName() As String Dim strItemNameBF() As String Dim iCol As Long Dim iRow As Long Dim rngMyRange As Range Set rngMyRange = Range("A3:C7") varItemName = rngMyRange ReDim strItemName(LBound(varItemName, 1) To UBound(varItemName, 1), _ LBound(varItemName, 2) To UBound(varItemName, 2)) '// Take a sample of the format strMyFormat1 = Range("A3").NumberFormat '// Apply format sample to all values For iRow = LBound(varItemName, 1) To UBound(varItemName, 1) For iCol = LBound(varItemName, 2) To UBound(varItemName, 2) strItemName(iRow, iCol) = Format(varItemName(iRow, iCol), strMyFormat1) Next iCol Next iRow '// Can also apply to only some values -- adjust loops. '// More loops go here if many format samples. '// If all cells have different formats, must use brute force -- slower. ReDim strItemNameBF(1 To rngMyRange.Rows.Count, _ 1 To rngMyRange.Columns.Count) For iRow = 1 To rngMyRange.Rows.Count For iCol = 1 To rngMyRange.Columns.Count strItemNameBF(iRow, iCol) = rngMyRange.Cells(iRow, iCol).Text Next iCol Next iRow
这篇关于从多单元格范围获取格式化值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!