VBA =Left() Function

Question

Lets keep it simple and avoid computer talk

Here's my data

Source location is Column D = "USA, TX"
Destination Location is Column E.

I need to get the country "USA" into Column E from Column D. In simple basic formula it would be

=Left(D,3)

But what is this in VBA?

Scott, how do I make the statement if column D has a cell range value from D2 through D whatever, since the data is going to be dynamic on a daily basis, the cell range of column D could vary from 200 to 2000. — Jundu, Jun 20 '18 at 21:48
google how to find the last row and loop through the cells in a column. — Scott Craner, Jun 20 '18 at 21:50
[Find last row and loop through range](https://stackoverflow.com/a/42103873/6706419) — urdearboy, Jun 20 '18 at 22:26

score 0 · Accepted Answer · edited Jun 23 '18 at 12:52

Maybe something like this. Code is untested (written on mobile).

Option Explicit

Sub extractCountries()
    Dim arrayOfValues() as variant
    Dim lastRow as long
    Dim rowIndex as long
    'Change these four lines as needed ' 
    Const SOURCE_COLUMN as string = "D"
    Const DESTINATION_COLUMN as string = "E"
    Const ROW_TO_START_FROM as long = 1
    With thisworkbook.worksheets("Sheet1")
        lastRow = .cells(.rows.count, SOURCE_COLUMN).end(xlup).row
        arrayOfValues = .range(SOURCE_COLUMN & ROW_TO_START_FROM ":" & SOURCE_COLUMN & lastRow).value2
        For rowIndex = lbound(arrayOfValues,1) to ubound(arrayOfValues,1)
            arrayOfValues(rowIndex,1) = vba.strings.left$(arrayOfValues(rowIndex,1),3)
            'If the logic should be "everything before the comma", use instr() function above.'
        Next rowIndex
        .range(DESTINATION_COLUMN & ROW_TO_START_FROM ":" & DESTINATION_COLUMN & lastRow).value2 = arrayOfValues
    End with
End sub

score 0 · Answer 2 · answered Jul 06 '19 at 07:58

0

Use like this

Dim s As String = "USA, TX"
s = Strings.Left(s, 3)

answered Jul 06 '19 at 07:58

ARN

173
2
9

VBA =Left() Function

2 Answers2