Excel VBA值在替换后保持字符串格式 [英] Excel VBA value remains string format after replacment

问题描述

我是Excel VBA中的新手。我找到一个脚本，可以帮助我将数据从一个工作表映射到另一个工作表，但在映射完成后，值格式刚刚更改。

我有两个工作表1是原始数据表，主数据表是映射数据的存储位置。请参阅以下表格结构：

表格1：

说明：

首页使用

业主

专业

CFO

秘书

主数据表格：

代码说明

001首页使用

002业主

003专业

004 CFO

005秘书

如您所见，主数据表第一列中的值为文本格式，即001，002等

下面的代码是将数据映射到主数据表中的第一列中的技巧，并使用它们替换表1中的描述。

 子映射（）
 
 Dim rng1 As Range，rng2 As Range，cel As Range 
 Dim StrMyChar As String，StrMyReplace As String 
 
使用ActiveWorkbook.Worksheets（主数据）
设置rng1 =。[B1：B5] 
结束
 
使用ActiveWorkbook.Worksheets（Sheet1）
设置r ng2 =。[A2：A6] 
结束
 
'需要替换
的文本循环列表对于每个cel在rng1.Cells 
 StrMyChar = cel.Value 
 StrMyReplace = cel.Offset（0，-1）.Value 
 
'替换文本
用rng2 
 .Replace什么：= StrMyChar，Replacement：= StrMyReplace， _ 
 SearchOrder：= xlByColumns，MatchCase：= False 
结束
'下一个单词/文本替换
下一个cel 
 
 End Sub 
  

运行代码后，我发现所有的001，002等都变成了1,2等

有没有办法保留001字符串格式？

谢谢。

解决方案

请尝试以下。请注意，它仍然强制替换格式，以使单元格中的值仍在技术上数字。这是Excel的替代功能的缺点 - 它的工作原理是因为它想要假设一切都是数字的。

请注意，您还将rng1设置为错误的范围，应该是b2-b6不是b1-b5

 使用ActiveWorkbook.Worksheets（Master Data）
设置rng1 =。[B2：B6]'请注意，这里有错误的范围
 End with 
 
'这将强制两个前导零，如果需要，请在替换$ b $之前调用它b Application.ReplaceFormat.NumberFormat =00＃
 
'然后添加ReplaceFormat：= true替换字符串
 .Replace什么：= StrMyChar，替换：= StrMyReplace，_ 
 SearchOrder：= xlByColumns，MatchCase：= False，ReplaceFormat：= True 
  

不幸的是 ReplaceFormat.NumberFormat =@不适用于Excel的内置替换。更好的选择，如果我们不想混淆Excel的内置替换方法，我们可以自己，快速和容易地：

 选项比较文本'用于区分大小写的比较
子映射（）
 
 Dim rngLookup As Range 
设置rngLookup = ActiveWorkbook.Worksheets（主数据）[ B2：B6] 
 
 Dim rngReplace As Range 
设置rngReplace = ActiveWorkbook.Worksheets（Sheet1）。[A2：A6] 
 
 Dim cell As Range， cellLookup As Range 
对于每个单元格在rngReplace 
 Dim val As String 
 val = cell.Value 
对于每个cellLookup在rngLookup 
如果cellLookup.Value = val Then 
 cell.NumberFormat =@
 cell.Value = cellLookup.Offset（0，-1）.Value 
退出
结束如果
下一个
 Next 
 End Sub 
  

此代码循环遍历Sheet 1中的每一行，然后在主表中搜索正确的条目，但在复制之前将数字格式设置为@。你应该是好的。

如果你要使用大量单元格，请考虑转动 Application.ScreenUpdating 关闭，然后再运行程序。这将加快速度，因为它不必担心在工作时呈现在屏幕上。

另一个非VBA想法它保留原始值并在其旁边添加数据：

您还可以使用没有任何VBA代码的Vlookup来获取此信息（尽管在不同的列中）。如果您将描述转换为列A，将代码转换到主表上的列B，则可以转到Sheet1，突出显示列B 中的单元格，然后键入以下公式：

  = VLOOKUP（A2：A6，'Master Data'！A2：B6,2，FALSE）
  / pre> 
 
 
不要输入，而是点击控制 +  Shift  + 输入。这创建了所谓的数组公式。这不代替您，但在旁边的列中提供数据。如果您需要另外一种获取方式，只需将其作为一些额外的信息。
 
 
 
您还可以使用Range.Formula属性为VBA中的单元格设置公式并将其设置为上面的vlookup公式
 
I am a total newbie in Excel VBA.  I find a script that can help me map data from one worksheet to another, but after the mapping is done, the value format just changed.

I have two sheets, Sheet 1 is the raw data sheet, and Master Data sheet is where the mapping data are stored.  Please see the table structures below:

Sheet 1:

Description:

Home Use

Business Owner

Professional

CFO

Secretary

Master Data sheet:

code Description

001 Home Use

002 Business Owner

003 Professional

004 CFO

005 Secretary

As you may see the values in the first column in the Master Data sheet are in text format, ie 001, 002, etc

The code below does the trick to map the data in the first column in Master Data sheet and use them to replace the description in Sheet 1.
Sub mapping()

Dim rng1 As Range, rng2 As Range, cel As Range  
Dim StrMyChar As String, StrMyReplace As String

With ActiveWorkbook.Worksheets("Master Data")
    Set rng1 = .[B1:B5]
End With

With ActiveWorkbook.Worksheets("Sheet1")
    Set rng2 = .[A2:A6]
End With

'loop down list of texts needing replacing
For Each cel In rng1.Cells
    StrMyChar = cel.Value
    StrMyReplace = cel.Offset(0, -1).Value

    'replace text
    With rng2
        .Replace What:=StrMyChar, Replacement:=StrMyReplace,_  
         SearchOrder:=xlByColumns, MatchCase:=False
    End With
    'Next word/text to replace
Next cel

End Sub  
After running the code, I find all the 001, 002, etc all got changed to 1, 2, etc.

Is there a way for me to preserve the 001 string format?

Thanks.
解决方案
Try this below. Note that it still forces the replacement format, so that the values in the cells are still technically numbers. This is a drawback of Excel's replace functionality--its just how it works because it wants to assume that everything is numeric.

Note that you also had the rng1 set to the wrong range, it should be b2-b6 not b1-b5
        With ActiveWorkbook.Worksheets("Master Data")
            Set rng1 = .[B2:B6] ' Note that you had the wrong range here
        End With

        'this will force two leading zeros if necessary call it before the replace
        Application.ReplaceFormat.NumberFormat = "00#" 

        'then add ReplaceFormat:=true to your replace string
        .Replace What:=StrMyChar, Replacement:=StrMyReplace, _
                 SearchOrder:=xlByColumns, MatchCase:=False, ReplaceFormat:=True
Unfortunately ReplaceFormat.NumberFormat = "@" does not work with Excel's built in replace. The better option if we don't want to mess with Excel's built in replace method, we can do it ourselves, quick and easy:
Option Compare Text 'use this for case insensitive comparisons
Sub Mapping()

    Dim rngLookup As Range
    Set rngLookup = ActiveWorkbook.Worksheets("Master Data").[B2:B6]

    Dim rngReplace As Range
    Set rngReplace = ActiveWorkbook.Worksheets("Sheet1").[A2:A6]

    Dim cell As Range, cellLookup As Range
    For Each cell In rngReplace
        Dim val As String
        val = cell.Value
        For Each cellLookup In rngLookup
            If cellLookup.Value = val Then
                cell.NumberFormat = "@"
                cell.Value = cellLookup.Offset(0, -1).Value
                Exit For
            End If
        Next
    Next
End Sub
This code loops through each line in your Sheet 1, and then searches for the proper entry in the master sheet, but sets the Number Format to "@" before it copies it. You should be good.

If you are going to have to work with a LOT of cells, consider turning Application.ScreenUpdating off before running the procedure, and back on after. This will speed things up as it doesn't have to worry about rendering to the screen while it is working.

Another, non VBA idea that keeps both the original value and adds data next to it:

You could also get this information (albeit in a different column) using a Vlookup without any VBA code. If you switch your Descriptions to Column A and your Codes to Column B on the Master Sheet, you can then go to Sheet1, highlight the cells in Column B and type this formula:
=VLOOKUP(A2:A6,'Master Data'!A2:B6,2,FALSE)
Do not hit enter, but rather hit Control+Shift+Enter. This creates what is called an Array formula. This doesn't do a replace for you, but offers the data in the column next to it. Just throwing this out there as some extra information if you needed another way of getting it.

You could also set the formula for a cell in VBA using the Range.Formula property and setting it to the vlookup formula above

                        
这篇关于Excel VBA值在替换后保持字符串格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！