使用左函数 [VBA]
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8340525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using the left-function [VBA]
提问by MrBiz
I'm trying to create a simple macro that copys the two first numbers in each cell in a column and printing them in a different column. This is in a excel-document with more than 1 worksheet. I've tried for a while to write the code, but with no prior experience, I have a hard time figuring out what to do. I know the "left()"-function is able to do this, but I don't know how I define which column to draw data from and to which column it will be printed. Any help will be greatly appreciated.
我正在尝试创建一个简单的宏，该宏复制一列中每个单元格中的前两个数字并将它们打印在不同的列中。这是在一个超过 1 个工作表的 excel 文档中。我已经尝试了一段时间来编写代码，但由于之前没有任何经验，我很难弄清楚该怎么做。我知道“left()”函数能够做到这一点，但我不知道如何定义从哪一列绘制数据以及将数据打印到哪一列。任何帮助将不胜感激。
回答by aevanko
With no prior experience to writing VBA code, I am going to reccommend you stick to the formula method of doing. Honestly, even if you were familiar with VBA, you might still opt to use the formula.
由于没有编写 VBA 代码的经验，我建议您坚持使用公式方法。老实说，即使您熟悉 VBA，您仍然可能会选择使用该公式。
A formula is put into the actual cell you want the value to be copied.
将公式放入要复制值的实际单元格中。
=LEFT(sourceCell, #of characters you want)
This is how it would look:
这是它的外观：
=LEFT(Sheet1!A1, 2)
Think of it as saying "this cell shall equal the first n characters in cell OO, starting from the left".
把它想象成说“这个单元格应该等于单元格OO中的前n个字符，从左边开始”。
Once you are done with your formula, if you don't need it to be binded to the source anymore (if the sourceCell changes, so does the cell with the LEFT formula), you can highlight the cells, Ctrl + C to copy, then right-click and select Paste Special. Then select VALUEand hit OK and now the cells are hard-coded with the value they were showing, as if you typed it yourself.
完成公式后，如果您不再需要将其绑定到源（如果 sourceCell 更改，带有 LEFT 公式的单元格也会更改），您可以突出显示单元格，Ctrl + C 复制，然后右键单击并选择Paste Special。然后选择VALUE并点击 OK，现在单元格使用它们显示的值进行硬编码，就好像您自己输入一样。
Once you master using formulas, the next step is VBA. Don't go confusing yourself by jumping into VBA and writing code for ranges, etc. if you aren't comfortable with using =LEFT yet. One step at a time, and you'll be a pro before you know it. :)
一旦掌握了公式的使用，下一步就是 VBA。如果您还不习惯使用 =LEFT，请不要通过跳入 VBA 并为范围等编写代码而使自己感到困惑。一步一个脚印，不知不觉中你就会成为一名专业人士。:)
回答by Maverik
Here is a quick sample sub to get you started:
这是一个快速示例子，可帮助您入门：
Public Sub LeftSub()
Dim SourceRange As Range, DestinationRange As Range, i As Integer
'Define our source range as A1:A10 of Sheet1
Set SourceRange = Sheet1.Range("A1:A10")
'Define our target range where we will print.
'Note that this is expected to be of same shape as source
Set DestinationRange = Sheet1.Range("B1:B10")
'Iterate through each source cell and print left 2 bits in target cell
For i = 1 To SourceRange.Count
DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)
Next i
End Sub
回答by Alex K.
How about
怎么样
Sub foo()
Dim cell As Range
Dim sourceRange As Range
'//define the source column - looks for contiguous downward data from A1;
Set sourceRange = Range(Sheets("Sheet1").Range("A1"), Selection.End(xlDown))
'//iterate each cell
For Each cell In sourceRange
If IsEmpty(cell.Value) Then Exit For
'//example to place the value in corresponding row of column B in sheet 2
Sheets("Sheet2").Range("B" & cell.Row).Value = Left$(cell.Value,2)
Next
End Sub
Or an equivalent formula (in the destination cell)
或等效公式（在目标单元格中）
=LEFT(Sheet1!A1,2)
