如何将粗体文本、日期和时间连接到特定单元格?

huangapple go评论59阅读模式
英文:

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种:

  1. 使用.Text

    .Text 返回一个表示屏幕上显示的内容的字符串。
    使用.Text通常不是一个好主意,因为你可能会得到####(如果列宽不足以显示完整文本)。
    你可以像这样从单元格中读取它:

    LN7B = Planilha3.Range("I3").Text

  2. 使用带有硬编码格式字符串的Format()

    正如评论中所建议的,你可以使用:

    LN7B = Format(Planilha3.Range("I3"), "dd/mm/yyyy hh:mm AM/PM [BR]")

    这有一个优点,可以避免出现实际日期之外的####,但不方便的地方在于,如果单元格的格式更改,输出不会更改。

  3. 使用根据单元格格式更改的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(&quot;I3&quot;).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(&quot;I3&quot;), &quot;dd/mm/yyyy hh:mm AM/PM &quot;&quot;[BR]&quot;&quot;&quot;)

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(&quot;I3&quot;).Value2, Planilha3.Range(&quot;I3&quot;).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.

huangapple
  • 本文由 发表于 2023年6月16日 01:54:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/76484321.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定