There are cases when we would like to manipulate a column based on a value from another column. Vlookup and Index match are great but we need to do it over and over again and fast. VBA is just for then

Report

OrderDate Region Rep Item Units UnitCost Total
1/6/2016 East Jones Pencil 95 1.99 189.05
1/23/2016 Central Kivell Binder 50 19.99 999.50
2/9/2016 Central Jardine Pencil 36 4.99 179.64
2/26/2016 Central Gill Pen 27 19.99 539.73
3/15/2016 West Sorvino Pencil 56 2.99 167.44
4/1/2016 East Jones Binder 60 4.99 299.40
4/18/2016 Central Andrews Pencil 75 1.99 149.25
5/5/2016 Central Jardine Pencil 90 4.99 449.10
5/22/2016 West Thompson Pencil 32 1.99 63.68
6/8/2016 East Jones Binder 60 8.99 539.40
6/25/2016 Central Morgan Pencil 90 4.99 449.10
7/12/2016 East Howard Binder 29 1.99 57.71
7/29/2016 East Parent Binder 81 19.99 1,619.19
8/15/2016 East Jones Pencil 35 4.99 174.65

Let’s say we want to segregate Reps into Team A and Team B and add that as a new column ‘Team’ then?

 

For ease, lets assume that the same is on a sheet named ‘Config’ in the same workbook.

names

 

‘————————————————————-

Sub MyMacro()

‘ let’s start by assigning objects the sheets to objects.

Dim ConfigSheet As Worksheet
Dim SourceSheet As Worksheet
Set ConfigSheet = ActiveWorkbook.Worksheets(“Config”)
Set SourceSheet = ActiveWorkbook.Worksheets(“Data”)
‘ Lets add the Reps and Let’s Team to an array

Dim Reps() As String
Dim Team() As String

‘ Lets get the lastRow of the sheet.
lastRow = ConfigSheet.UsedRange.Rows.Count

‘ Define both the arrays

ReDim Reps(lastRow)
ReDim Team(lastRow)

‘ Add the data to Array’s

For i = 2 To ConfigSheet.UsedRange.Rows.Count

‘ The format is Sheet.cells(Row,Column)

Reps(i) = ConfigSheet.Cells(i, 1)
Team(i) = ConfigSheet.Cells(i, 2)
Next i
‘ Now active the SourceSheet

SourceSheet.Activate

‘ Now find the lastColumn of the Sheet

LastCol = SourceSheet.UsedRange.Columns.Count

‘ make a label for last Column + 1, No guesses why + 1 ?

SourceSheet.Cells(1, LastCol + 1) = “Team”

‘Now lets run the loop to add the team name

‘ Lets find the column name

Reps_Column = getColFromAddr(getAddress(SourceSheet, “Rep”))

For i = 2 To SourceSheet.UsedRange.Rows.Count

For j = 0 To UBound(Reps())
If SourceSheet.Cells(i, Reps_Column) = Reps(j) Then

SourceSheet.Cells(i, LastCol + 1) = Team(j)

End If

Next j
Next i
End Sub

 

‘————————————————————-

The output is this:

output1.JPG

The file is attached here and there are some more functions for help here are in Standard Module.

make-new-column-vba

Also, see – Find a column by name – VBA Excel

Cheers

CuriousJatin

Advertisements