Excel中的TextToColumns方法在VBA中忽略公式 [英] Excel TextToColumns method in VBA to ignore formulas

问题描述

首先，让我说，我希望在同一篇文章中提出这样的礼节是正确的。这一切都是一样的，但我不知道这是否正确。所以，如果我不对，请纠正我，以便我可以修复它。

我创建了一个宏来执行TextToColumns。我编辑并清理了宏。这是正常工作，直到我开始使用公式将数据传播到这些字段，而不是手动复制数据，这是我以前做的。

现在， ve在我的工作表的模块代码上扩展，并意识到我在几个月前写的方法干扰了现在将值复制到我最初执行TextToColumns的字段中的公式。

简而言之，有没有办法告诉.TextToColumns采取其工作范围的值，忽略公式？

  Sheet4.Range（categorySrcColumn& srcCategoryColLastRow）
 .TextToColumns _ 
目标：=范围（categoryDe​​stColumn），_ 
其他：= True，_ 
 OtherChar：=/，_ 
 FieldInfo：= Array（1,1），_ 
 TrailingMinusNumbers：= true 
  

我检查了TextToColumns搜索xlValues的文档，但是找到这样的东西。

----------附录-----------

Rachel。感谢您的投入也许你可以多一点协助。我已经插入了你的建议，但它根本没有做任何事情。我在这里采取了一些自由，希望结合你所需要的东西。免责声明：我绝对不是VBA开发者。我知道的是我从谷歌学到的东西我住在Unix / Linux世界，所以这是一个非常不同的鸟，我习惯了。

这是我到目前为止...

 公共函数sbTextToColumn（srcRange As String，dstRange As String）
 Dim vData As Variant 
 
 vData = Split（ActiveSheet.Range（srcRange）.Value，/）
 
'我需要按一下srcRange中的数据，然后才能将它分开。 
'这也是对数据的部分理性检查。 
 Range（srcRange）.Replace What：=I / R，Replacement：=IR，Lookat：= xlPart，_ 
 SearchOrder：= xlByRows，MatchCase：= False，SearchFormat：= False _ 
 ReplaceFormat：= False 
 
 Range（srcRange）.Replace What：=N / A，Replacement：=NA，Lookat：= xlPart，_ 
 SearchOrder ：= xlByRows，MatchCase：= False，SearchFormat：= False _ 
 ReplaceFormat：= False 
 
范围（srcRange）.Replace What：=，Replacement：=，Lookat： = xlPart，_ 
 SearchOrder：= xlByRows，MatchCase：= False，SearchFormat：= False _ 
 ReplaceFormat：= False 
 
 ActiveSheet.Range（dstRange）.Resize（ColumnSize： = UBound（vData）+ 1）= vData 
 sbTextToColumn =
结束函数
  

不幸的是，我无法发布电子表格的示例。这是一个封闭的，安全的网络。

现在，当我通过以下方式将其插入工作表： = sbTextToColumn（C836，E836） code>，没有任何反应。

这是我第一次尝试创建和使用UDF。这是我如何调用这个函数。我有一个工作表，前三列A-C从另一个电子表格中获取数据。那些列当然是公式。列D没有被使用，但是我在这里打电话给UDF，希望这样会工作。列E-I被保留用于T2C数据，并且通过拉取与列B相同的数据来使用列J（是的，冗余的，我知道的）。我的目标是，我想摆脱列J（它用于另一个工作表中的vlookup，我可能无法删除它，但我想）。理想情况下，我想从其他电子表格中提取源数据，理智检查它，将其拆分出来，并将排序的数据放在必要的n列中。

解决方案

包含UDF的函数只能向其调用者返回一个值。他们不能修改其他单元格。

这是Rachel的方法作为UDF包装：

 函数sbTextToColumn（rng As Range）As Variant 
 Dim vData As Variant 
 
 vData = Split（rng.Value2，/）
 ReDim保存vData（0到Application.Caller.Columns.Count  -  1）
 sbTextToColumn = vData 
结束函数
  

将其作为数组函数输入。在你的例子中，对于行2

• 选择范围 E2：I1


• 键入 = sbTextToColumn（C2）


• 按 Ctrl + Shift + 输入


• 将5个单元格范围向下复制多行根据需要

功能无法修改其他单元格意味着您无法在UDF中进行清理。

从您想要的示例代码

• 用IR替换I / R，使用NA替换N / A


• 删除空格


• 在数据分割成列之后执行此操作 E：I （因此I / R已经被分割成两个单元格）


• 将具有消息化数据的原始列C 公式替换为值 / li>
  

这是对的吗？

如果是这样，你最好不要使用UDF，而是使用 Sub （从快捷键中调用或添加到菜单中）

像：

  Sub sbTextToColumn（）
 Dim ws As Worksheet 
 Dim srcRng As Range 
 Dim rw As Range 
 Dim vData As Variant 
 Dim srcData As Variant 
 Dim dstData As Variant 
 Dim i As Long，j As Long 
 Dim maxCol As Long 
 
设置ws = ActiveSheet 
设置srcRng =范围（ws.Columns（3）.Cells（1，1），_ 
 ws .Columns（3）.Cells（ws.Columns（3）.Rows.Count）.End（xlUp））
 
 srcData = srcRng 
 ReDim dstData（LBound（srcData，1）To对于i = LBound（srcData，1）到UBound（srcData，1）
如果srcData（i，1）<> 然后
'拆分数据
 vData = Split（srcData（i，1），/）
 maxCol = UBound（vData，1）
如果maxCol> 5然后maxCol = 5 
对于j = 1到maxCol + 1 
 dstData（i，j）= vData（j  -  1）
下一个
 
'消除数据
 srcData（i，1）= Replace（srcData（i，1），I / R，IR，Compare：= vbTextCompare）
 srcData（i，1）= Replace（srcData i，1），N / A，NA，比较：= vbTextCompare）
 srcData（i，1）=替换（srcData（i，1），，，比较：= vbTextCompare ）
结束如果
下一个
 
'将结果放回单页
 srcRng = srcData 
 srcRng.Offset（0，2）.Resize（，5 ）= dstData 
 End Sub 
  

如果你真的想在拆分之前进行清理，拆分数据和消毒数据代码块的顺序。

First, let me say that I hope that it is proper SO etiquette to be asking this in the same post as my original. It's all in the same vein, but I'm not sure this is proper. So, please, if I'm not right, correct me so I can fix it.

I created a macro to perform a TextToColumns a while back. I edited and cleaned up the macro. It was working just fine until I started propagating data into those fields using formulas instead of manually copying data into them, which was what I used to do.

Now, I've expanded on my worksheet's module code and realized that the method I wrote months ago interferes with formulas that now copy the values into the fields that I originally was performing the TextToColumns.

In short, is there a way to tell .TextToColumns to take the values of the range in which it's working and ignore the formula?

 Sheet4.Range(categorySrcColumn & srcCategoryColLastRow)
       .TextToColumns _
            Destination:=Range(categoryDestColumn), _
            Other:=True, _
            OtherChar:="/", _
            FieldInfo:=Array(1,1), _
            TrailingMinusNumbers:=true

I checked the documentation for TextToColumns searching for xlValues but couldn't find anything like that.

---------- addendum -----------

Rachel. I appreciate your input. Perhaps you could assist a little bit more. I've plugged in your suggestion, but it simply is doing nothing. I've taken a few libterties here in hopes to combine what you've given with what I need. Disclaimer: I am by no means a VBA developer. What I know is what I've learned from google. I live in the Unix/Linux world so this is a very different bird to what I'm used to.

Here's what I have so far...

Public Function sbTextToColumn(srcRange As String, dstRange As String)
    Dim vData As Variant

    vData = Split(ActiveSheet.Range(srcRange).Value, "/")

    ' I need to massage the data in the srcRange a bit before I can separate it out. 
    ' This is a partial sanity check on the data as well.
    Range(srcRange).Replace What:="I/R", Replacement:="IR", Lookat:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False _
                    ReplaceFormat:=False

    Range(srcRange).Replace What:="N/A", Replacement:="NA", Lookat:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False _
                    ReplaceFormat:=False

    Range(srcRange).Replace What:=" ", Replacement:="", Lookat:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False _
                    ReplaceFormat:=False

    ActiveSheet.Range(dstRange).Resize(ColumnSize:=UBound(vData) + 1) = vData
    sbTextToColumn = ""
End Function

I cannot post an example of the spreadsheet, unfortunately. It's on a closed, secure network.

Now, when I plug this in in the worksheet via: =sbTextToColumn(C836, E836), nothing happens.

This is my first attempt at ever creating and using a UDF. Here's how I'm calling the function. I have a worksheet with the first three columns A-C grabbing data from another spreadsheet. Those columns are of course formulas. Column D is not used, but I'm placing the call to the UDF in here, hoping that that will work. Columns E-I are reserved for the T2C data and column J is used by pulling the same data as column B (yes, redundant, I know). My goal is that I'd like to get rid of column J (it's used for a vlookup in another worksheet and I may not be able to remove it but I'd like to). Ideally, I'd like to pull the source data from the other spreadsheet, sanity check it, split it out, and simply place the sorted data in the n columns necessary.

So, am I on the right track? I'm still working on this. If I make any progress, I'll post the progress. TIA!

解决方案

Functions, including UDFs, can only return a value to their caller. They cannot modify other cells.

Here is Rachel's method wrapped as a UDF:

Function sbTextToColumn(rng As Range) As Variant
    Dim vData As Variant

    vData = Split(rng.Value2, "/")
    ReDim Preserve vData(0 To Application.Caller.Columns.Count - 1)
    sbTextToColumn = vData
End Function

You enter it as a array function. In your example, say for row 2

• Select range E2:I1
• Type =sbTextToColumn(C2)
• Press Ctrl+Shift+Enter
• Copy the 5 cell range down for as many rows as needed

Functions not being able to modify other cells means you cannot do the sanitising in the UDF.

From your sample code you want to

• Replace I/R with IR, and N/A with NA
• Remove spaces
• Do this after the data is split out into columns E:I (so I/R will already be split into two cells)
• Replace the original Column C formulas with sanitised data as values

Is this right?

If so, you would be best to not used a UDF at all, but rather use a Sub (called from a quick key, or added to a menu)

Somthing like:

Sub sbTextToColumn()
    Dim ws As Worksheet
    Dim srcRng As Range
    Dim rw As Range
    Dim vData As Variant
    Dim srcData As Variant
    Dim dstData As Variant
    Dim i As Long, j As Long
    Dim maxCol As Long

    Set ws = ActiveSheet
    Set srcRng = Range(ws.Columns(3).Cells(1, 1), _
        ws.Columns(3).Cells(ws.Columns(3).Rows.Count).End(xlUp))

    srcData = srcRng
    ReDim dstData(LBound(srcData, 1) To UBound(srcData, 1), 1 To 5)

    For i = LBound(srcData, 1) To UBound(srcData, 1)
        If srcData(i, 1) <> "" Then
            ' Split data
            vData = Split(srcData(i, 1), "/")
            maxCol = UBound(vData, 1)
            If maxCol > 5 Then maxCol = 5
            For j = 1 To maxCol + 1
                dstData(i, j) = vData(j - 1)
            Next

            ' Sanitise data
            srcData(i, 1) = Replace(srcData(i, 1), "I/R", "IR", Compare:=vbTextCompare)
            srcData(i, 1) = Replace(srcData(i, 1), "N/A", "NA", Compare:=vbTextCompare)
            srcData(i, 1) = Replace(srcData(i, 1), " ", "", Compare:=vbTextCompare)
        End If
    Next

    ' Put results back in sheet
    srcRng = srcData
    srcRng.Offset(0, 2).Resize(, 5) = dstData
End Sub

If you actually want to sanitise before the split, simply swap the order of the Split data and Sanitise Data code blocks.

这篇关于Excel中的TextToColumns方法在VBA中忽略公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！