CODEWITHSUNDEEP
excelvba
MrBiz
MrBiz

Reputation: 29

Using the left-function [VBA]

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.

Upvotes: 1

Views: 44288

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

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.

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".

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 VALUE and hit OK and now the cells are hard-coded with the value they were showing, as if you typed it yourself.

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. :)

Upvotes: 10

Alex K.
Alex K.

Reputation: 175816

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)

Upvotes: 2

Maverik
Maverik

Reputation: 5671

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

Upvotes: 2

Related Questions