用公式中的答案替换单元格

I have an excel sheet with a bunch of headers, but some do not have dashes. I need them all to have dashes, except total.

我有一个excel表格，上面有一堆标题，但有些没有破折号。我需要它们都有破折号，除了total。

Ex:

例:

I've written the formula that will add the dashes to the headers:

我写了一个公式，可以把破折号加到标题上:

=IF(A1="","",IF(A1="TOTAL","TOTAL",REPLACE(SUBSTITUTE(A1,"-",""), 1, 0, "-")))

However, the problem is that I need this in a VBA macro, and it needs to replace the existing headers with keeping the same formatting.

但是，问题是我需要在VBA宏中使用它，它需要用相同的格式替换现有的头文件。

I'm not sure how to write this, this is what I got so far:

我不知道怎么写，这是我到目前为止得到的

Sub AddDash()
Dim MaxColumn As String
MaxColumn = Range("AY1").End(xlToLeft)
For i = 1 To TotalRows
Formula = "=IF(A1="","",IF(A1="TOTAL","TOTAL",REPLACE(SUBSTITUTE(A1,"-",""), 1, 0, "-")))"
Next i
End Sub

Is this possible to do?

这可能吗?

I've seen something similar, but I do not understand it enough to make it work for my situation

我也见过类似的情况，但我不太理解它，无法适应我的情况

With Sheet1
    With Range(.Cells(3,3), .Cells(.Rows.Count,3).End(xlup))
        With .Offset(0, Sheet1.UsedRange.Columns.Count +3)
            .FormulaR1C1 = "=REPLACE(REPLACE(SUBSTITUTE(RC3,""-"",""""), 9, 0, ""-""), 7, 0, ""-"")"
         End With
         .Value = .Offset(0, Sheet1.UsedRange.Columns.Count +3).Value
         .Offset(0, Sheet1.UsedRange.Columns.Count +3).EntireRow.Delete
    End With
End With

Thank you for the help!

谢谢你的帮助!

1 个解决方案

#1

Is this what you are trying? I have commented the code but if you still have a question then feel free to ask.

这就是你想要的吗?我已经注释了代码，但是如果您还有问题，请尽管问。

Sub AddDash()
    Dim ws As Worksheet
    Dim lCol As Long, i As Long

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Find the last column in row 1
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        '~~> Loop through the columns in row 1
        For i = 1 To lCol
            '~~> Check 1 : Cell is not empty
            '~~> Check 2 : Cell doesn't have TOTAL
            '~~> Check 3 : Cell Doesn't already have a Dash
            If Len(Trim(.Cells(1, i).Value)) <> 0 And _
               UCase(Trim(.Cells(1, i).Value)) <> "TOTAL" And _
               Left(Trim(.Cells(1, i).Value), 1) <> "-" Then
                '~~> Add Dash
                .Cells(1, i).Value = "-" & .Cells(1, i).Value
            End If
        Next i
    End With
End Sub

Screenshot

截图