英文:
How can I concatenate a bold text, date and time into a specific cell?
问题
基本上,我试图将另一个工作表中提供的特定文本(以粗体格式显示)、日期和时间连接到特定单元格中。
以下代码帮助我实现这一目标:
Sub Atualizar_Abertura()
Dim LN7A As String, LN7B As String
LN7A = "Próximo comunicado:"
LN7B = Planilha3.Range("I3").Text
Range("D23").Value = LN7A & " " & LN7B
Range("D23").Font.Bold = False
Range("D23").Characters(Start:=1, Length:=Len(LN7A)).Font.Bold = True
Planilha5.Rows("23").AutoFit
End Sub
但是源单元格具有"dd/mm/yyyy hh:mm AM/PM [BR]"的NumberFormat,应显示为:
所以,我期望的是:将文本连接起来,就像已经完成的那样,并且将日期/时间与我在单元格I3中使用的NumberFormat连接起来。
我在下面的图像中进行了示例说明:
有没有办法实现这个目标?
英文:
Basically, I'm trying to concatenate a specific text (in bold format), the date and time informed in another worksheet into a specific cell.
This code below help me to do that:
Sub Atualizar_Abertura()
Dim LN7A As String, LN7B As String
LN7A = "Próximo comunicado:"
LN7B = Planilha3.Range("I3")
Range("D23").Value = LN7A & " " & LN7B
Range("D23").Font.Bold = False
Range("D23").Characters(Start:=1, Length:=Len(LN7A)).Font.Bold = True
Planilha5.Rows("23").AutoFit
End Sub
And this is the result
But the source cell has the NumberFormat "dd/mm/yyyy hh:mm AM/PM [BR]" to be shown as:
So, what I'm expecting is: Concatenate the text as it's already being done and Date/time with the NumberFormat that I used into the cell I3.
I exemplified in an image:
Is there any way to do it?
答案1
得分: 1
你有几种选择。以下是其中3种:
-
使用
.Text
.Text
返回一个表示屏幕上显示的内容的字符串。
使用.Text
通常不是一个好主意,因为你可能会得到####
(如果列宽不足以显示完整文本)。
你可以像这样从单元格中读取它:LN7B = Planilha3.Range("I3").Text
-
使用带有硬编码格式字符串的
Format()
正如评论中所建议的,你可以使用:
LN7B = Format(Planilha3.Range("I3"), "dd/mm/yyyy hh:mm AM/PM [BR]")
这有一个优点,可以避免出现实际日期之外的
####
,但不方便的地方在于,如果单元格的格式更改,输出不会更改。 -
使用根据单元格格式更改的
Format()
LN7B = Format(Planilha3.Range("I3").Value2, Planilha3.Range("I3").NumberFormatLocal)
通过使用这种方法,你始终确保获得与值来自的单元格相同的自定义格式。请注意,某些内置数字格式可能会有异常情况,如在此问题中讨论的问题。
英文:
You have a few options. Here's 3 of them:
<h2>1. Use .Text
</h2>
> .Text gives you a string representing what is displayed on the screen
> for the cell. Using .Text is usually a bad idea because you could get
> #### [if the column is not wide enough to display the full text]
<sup>source</sup>
You can use it to read from the cell like this:
LN7B = Planilha3.Range("I3").Text
<h2>2. Use Format()
with hardcoded format string</h2>
As suggested by T.M. in the comments, you can use
LN7B = Format(Planilha3.Range("I3"), "dd/mm/yyyy hh:mm AM/PM ""[BR]""")
That has the advantage of avoiding issues where you could have "####" appear instead of the real date, but the inconvenient is that if the formatting of the cell changes, the output won't change.
<h2>3. Use Format()
that changes based on the cell's formatting</h2>
LN7B = Format(Planilha3.Range("I3").Value2, Planilha3.Range("I3").NumberFormatLocal)
By using this approach, you are always sure that you'll get the same custom formatting as the cell the value came from. Note that there might be exceptions with some built-in number format such as discussed in this question.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论