Update Sales Territory Shifts/Changes through VBA

[vishwakarma]

Hi Guys,

I work in Sales Department and work on many reports related to sales. One of the report is Territory Alignment to the Sales reps. I’ve a formula sheet where I can update the changes and assign sales rep to the territory according to the change. But here the problem is that whenever there is mass change (means where ever the territories having zip split are shifted to multiple sales reps) I’ve to go and check each records and update the formula sheet accordingly and this consume ample amount of time and concentration. Is there any way by which we can create this sheet in vba in a user form or anything…

Just to be more precise:-

The territory sheet that I have, contains un-split and split of the states according to the zips, for e.g :- State Alaska(AK) is wholly owned by a particular sales rep, where California(CA) is splitted between multiple sales reps by zips.

So, if there is any change let’s say in both of these cities, For Alaska it is fine as there is no split for zip codes but for California I’ve to check the zip split one by one and have to do the changes and this consume much of time.

I’ve also attached a sample sheet for the reference.


Any help is appreciated.

[Bob Phillips]

How would you know what to replace each name with? If it were Jonny always gets replaced by say NameX then you could set up a before and after list and just iterate that. If it is less structured, I cannot see how you could write code to do it.

[vishwakarma]

Sorry for not being too clear for the first time,


I don’t want to update the territory sheet and instead want another macro sheet where we can track the sales reps on the basis of the territory alignment, just like I track the sales reps on the basis of formula sheet (attached in another tab – in red color).

Just to be more precise, let say I have list of state, zip and country in a sheet and want to have the names of all the sales rep as per the territory, so for this I have created the attached formula sheet. I simply paste the list of state, zip and country in the column A,B,C and track all the sales reps through this. The formula is working great but my main problem is to update the changes in the territory that I do manually . I’ve highlighted the columns(in Orange) in the formula tab where I update the sales reps manually according to the zip codes split whenever there are changes. I want this manual work to be replaced by a macro sheet instead.

Is there any way by which we can replace this manual updating of the sales rep in the formula sheet.



This would be a great help for me.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim colReps As Collection
Dim vecOldReps As Variant
Dim vecNewReps As Variant
Dim sh As Worksheet
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

Set sh = Worksheets("Sample Terri.")
With sh

Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set colReps = New Collection
On Error Resume Next
For i = 2 To Lastrow

colReps.Add i, .Cells(i, "C")
Next i
On Error GoTo 0

ReDim vecOldReps(1 To colReps.Count)
ReDim vecNewReps(1 To colReps.Count)
For i = 1 To colReps.Count

vecOldReps(i) = .Cells(colReps.Item(i), "C").Value2
vecNewReps(i) = .Cells(colReps.Item(i), "D").Value2
Next i
End With

Call UpdateRep("N", vecOldReps, vecNewReps)
Call UpdateRep("P", vecOldReps, vecNewReps)
Call UpdateRep("T", vecOldReps, vecNewReps)

Application.ScreenUpdating = True
End Sub

Private Sub UpdateRep(ByVal col As String, ByVal OldReps As Variant, NewReps As Variant)
Dim Lastrow As Long
Dim Reprow As Long
Dim i As Long

With Worksheets("Formula")

Lastrow = .Cells(.Rows.Count, col).End(xlUp).Row
For i = LBound(OldReps) To UBound(OldReps)

.Columns(col).Replace What:=OldReps(i), _
Replacement:=NewReps(i) & "~", _
LookAt:=xlWhole
Next i
.Columns(col).Replace What:="~~", _
Replacement:="", _
LookAt:=xlPart
End With
End Sub
[/vba]

[vishwakarma]

Hi xld,

Thx for the code, but i think it still not working. I tried the code by changing the new sales rep value in D2, though it change the name in the formula sheet for AK, but it is also changing the names for NC,NY,CA& DC(zip split column) as well...


may be i might be doing something wrong... pls help..


Thanks,

[vishwakarma]

what I need is when I change the sales rep for AK, it should update the reps name for the state of AK, if I update the sales rep for CA where zip is ([92100-92130]) then it should replace the old rep with the new for zip code starting from 92100 to 92130 for CA.

looking forward to this... Thanks..

[Bob Phillips]

Why not just use VLOOKUP formulas in the Formula sheet.

[vishwakarma]

I'm already using VLOOKUP for tracking the sales reps as per the territory. If you check the column D in the formula tab of the sheet, there are multiple VLOOKUP we have used to track the sales reps as per territory.I simply feed the date for state,zip and country in the first three columns of the formula sheet and track out all the sales reps for those state through the formula...

My problem is not tracking the sales reps, it is already being taken care of through the VLOOKUP function. The main issue which I'm facing is to update the sales reps whenever there are shifts in the territory in the columns N,P and T of the formula sheet. I've to check the updates in the territory and then have to update the names of the reps manually according the zip codes per state. For e.g. Let say, In CA "California - part SD [92100-92130]" (in Sample Terri. tab) previously it was Alex as a sales rep and now this territory has been assigned to Jonny, so to transfer the territory from Alex to Jonny, I have to replace Alex to Jonny from zip code starting from 92100 to zip 92130 in CA manually. This is the part I hate the most in this sheet and want to eliminate either through vb(preferable) or through any advanced formula(if cannot be done through vb)..

[vishwakarma]

ok guys...

if this cannot not be done.Can anyone help me in creating a form though which I can do the following things...

Basically what i want to do is to replace a value based on the multiple criteria through macro..

For e.g. I want to replace the name “X” with “Y” in column “C” of the attached sheet based on the criteria like “state” should be CA (column A) and zip code( in column B) equaling or in between 1001 and 1006. So if both these criteria are met only then it should replace the X with Y.

Any help appreciated…