在VBA中编写公式会导致类型不匹配13 | 在VBA中编写公式会导致类型不匹配13

本文介绍了在VBA中编写公式会导致类型不匹配13的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

主要问题:是否可以在VBA中编写将值另存为变量的公式，而无需将其放在工作表的单元格中?

Overlying question: Is it possible to code formulas in VBA that save the values as a variable, without putting them in a cell on the worksheet?

我从"My_Date = Left(Replace")开始的行出现错误...我认为这对于"My_Take = Right(Replace")行也是一个问题.

I get the error for the line starting with "My_Date = Left(Replace" ... I assume it will also be an issue for the "My_Take = Right(Replace" line.

该函数应该从字符串中取出数字:

The function is supposed to take numbers out of strings:

Public Function SplitText(pWorkStr As String, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkStr)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkStr, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

这会将数字格式化为日期，并且不带任何字母:

This formats the numbers as a date and take without any letters:

Sub Fill_Date_and_Take()

    Dim NAMEoFILE As String

    Dim My_Date As String
    Dim My_Take As String

    NAMEoFILE = "I love dogs 09-20-17 Take Number 2.xlsx"

    My_Date = Left(Replace(Replace(Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 7)
        MsgBox (My_Date)

    My_Take = Right(Replace(Replace(Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 1)
        MsgBox (My_Take)

End Sub

推荐答案

我对Stack Overflow有点陌生，所以我不确定如何将GSerg的注释设置为答案，但是我要做的就是更改每个替换"到"WorksheetFunction.Replace"！

I am a bit new to Stack Overflow so I wasnt sure how to set GSerg's comment to the answer, but all I had to do was change each "Replace" to "WorksheetFunction.Replace"!

My_Date = Left(WorksheetFunction.Replace(WorksheetFunction.Replace(WorksheetFunction.Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 7)

My_Take = Right(WorksheetFunction.Replace(WorksheetFunction.Replace(WorksheetFunction.Replace(SplitText(NAMEoFILE, True), 2, 0, "-"), 5, 0, "-"), 8, 0, "_"), 1)

谢谢GSerg！

这篇关于在VBA中编写公式会导致类型不匹配13的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！