VBA populate a cell based on values from several worksheets and have error handling

[jconkl02]

I have been learning VBA over the last two months while trying to re-write a macro that my predecessor did not have time to finish/work out the bugs before he took another job. This forum has been amazing, but I have come to something that I can't find a fix for and I am hoping someone can help me out. I have included my sample data below.


I need to populate the column 'Ticket Owner" in worksheet "Data" based off of values in "Data" and two other worksheets ("Director" and "Market"). Everything stems from what is in Worksheet "Data" Column B (Queue).

• If the value in Worksheet "Data" Column B (Queue) ends with "RN" then use the value in Column C (Wrls_Mkt) to lookup the name of the Director in Worksheet "Market" and paste that name into "Ticket Owner" in worksheet "Data".
• If the value in Worksheet "Data" Column B (Queue) DOES NOT end with "RN" then use the value in Column B (Queue) to look up the name in column B (Director) in Worksheet "Director" and paste that name into "Ticket Owner" in worksheet "Data".
• If either lookup fails to find a matching value then populate Column A of worksheet "Data" with "UNKNOWN"

My predecessor was attempting to accomplish this with this IF and ISERROR function:


********ActiveCell.FormulaR1C1 = "=IF(AND(ISERROR(VLOOKUP(RC[-10],'Director to Queue'!R2C1:R250C2,2,FALSE)),RIGHT(RC[-10],2)=""RN""),VLOOKUP(RC[-7],'Market to Director'!R2C1:R500C2,2,FALSE),VLOOKUP(RC[-10],'Director to Queue'!R2C1:R250C2,2,FALSE))"

I'll be honest I don't understand all of the components of his code, but one problem that keeps popping up is that if there is a new city name or queue name in the Data worksheet that has not been added to the other two worksheets the macro crashes.


I hope this makes sense. Thanks for any help that you can give me.


Excel 2010

	A	B	C
FOTORN	Toledo : Toledo
V1ERRN	Miami / West Palm : Miami
V1ALRN	Washington DC : Washington DC
V1SARN	Miami / West Palm : Non-MSA
V1SARN	DFW : Fort Worth / Arlington
FOTORN	DFW : Fort Worth / Arlington
V1AL	West Washington : Seattle / Bellevue / Everett
FOTORN	Pittsburgh : Sharon
V1ERRN	Tampa : Tampa / St. Petersburg / Clearwater
V1ALRN	DFW : Fort Worth / Arlington
V1SARN	Upper Central Valley : Modesto
V1SARN	Upper Central Valley : Modesto
FOTORN	Lower Central Valley : Bakersfield
V1AL	West Texas : Non-MSA
FOTORN	Myrtle Beach : Wilmington, NC
V1ERRN	Myrtle Beach : Wilmington, NC
V1ALRN	Memphis : Memphis
V1SARN	Baltimore : Baltimore
V1SARN	Chicago : Chicago
FOTORN	North Wisconsin : Non-MSA
V1AL	Myrtle Beach : Jacksonville, NC
V1AL	New York City : New York
FOTORN	West Texas : Non-MSA
FOTORN	Ft. Wayne / South Bend : Fort Wayne

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Ticket Owner[/TD]
[TD="align: center"]Queue[/TD]
[TD="align: center"]Wrls_Mkt[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

</tbody>

Data​

Excel 2010

	A	B
Baltimore : Baltimore	Peter Pan
Baltimore : Non-MSA	Peter Pan
Boston : Barnstable / Yarmouth	Peter Pan
Boston : Boston	Peter Pan
Idaho : Boise City	Captain Hook
Inland Northwest : Non-MSA	Captain Hook
Inland Northwest : Richland / Kennewick / Pasco	Captain Hook
Inland Northwest : Spokane	Captain Hook
Cincinnati : Hamilton / Middletown	Tinker Bell
Cincinnati : Lima	Tinker Bell
Cincinnati : Non-MSA	Tinker Bell
Cleveland : Akron	Tinker Bell
West Kentucky : Non-MSA	Wendy Darling
West Kentucky : St. Louis	Wendy Darling
Alabama : Anniston	Wendy Darling
Alabama : Birmingham	Wendy Darling
Alabama : Decatur, AL	Wendy Darling
Arkansas : Fayetteville / Springdale / Rogers	Tiger Lily
Arkansas : Fort Smith	Tiger Lily
Arkansas : Little Rock / North Little Rock	Tiger Lily
Arkansas : Non-MSA	Tiger Lily

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Market[/TD]
[TD="align: center"]Director[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

</tbody>

Market​

Excel 2010

	A	B	C
PROI	Mr. Smee	SACO
RSAN	George Scourie	OEM
ECPT	Black Gilmour	SACO
MOT2	Canary Robb	SACO
T2LE	Bill Jukes	SACO
VBO2	Black Pirate	SACO
VBSC	Alf Mason	SACO
WBHE	Robert Mullins	SACO
WBHSCB	George Scourie	SACO

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Queue[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]Group[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Director​

 

[sergioMabres]

Hi J
Your problem is kind of hard to solve because the sample data you have posted does not match with your formula where RC[-10] and RC[-7] gives us a clue on where is the active cell when the macro is run, much to the right
Also data rows are cut to your own caprice not showing the complete domain that is necessary to examine where is/are errors.
Nevertheless for your statement when "there is a new city name or queue name in the Data worksheet" I can tell you that all formulas will crush if you don't add the values to 'Director to Queue' and/or 'Market to Director'
Your are using a VLOOKUP and this formula needs to find the matching values to make their propose.
Also you will get an error when 'Director to Queue' has more the 250 rows and when 'Market to Director' has more then 500 rows
My advice is for you read some information about VLOOKUP to understand how the formula works
Cheers
Sergio

 

[jconkl02]

Hi J
Your problem is kind of hard to solve because the sample data you have posted does not match with your formula where RC[-10] and RC[-7] gives us a clue on where is the active cell when the macro is run, much to the right
Also data rows are cut to your own caprice not showing the complete domain that is necessary to examine where is/are errors.
Nevertheless for your statement when "there is a new city name or queue name in the Data worksheet" I can tell you that all formulas will crush if you don't add the values to'Director to Queue' and/or 'Market to Director'
Your are using a VLOOKUP and this formula needs to find the matching values to make their propose.
Also you will get an error when'Director to Queue' has more the 250 rows and when 'Market to Director' has more then 500 rows
My advice is for you read some information about VLOOKUP to understand how the formula works
Cheers
Sergio

Sergio,
Thanks for responding. The VLOOKUP in the formula is what the person that maintained this report used. I only added it to the post to give you an idea of how it was being done before.

I am just looking for the most efficient way of looking up the data.

The active cells would be column A of "Data". For example, the script would start on A2 and it would look at the value in B2. If B2 ends with "RN" then the value of C2 would be used in column A of the worksheet "Market" to find the name of the owner of that market and populate A2 of "Data" with that value. If B2 did not end with "RN" then the value of B2 would be used in column A of the worksheet "Director" to find the name of the owner of that queue and populate A2 of "Data" with that value.

In either case, if a match can't be made then A2 would be populated with "UNKNOWN"

 

[sergioMabres]

Hi J
That is quite different, in this case, the case you describe in your second mail, you do not need a macro/VBA you just need a formula in A2

=IFERROR(IF(RIGHT(B2,2)="RN",VLOOKUP(C2,market!A:B,2,0),VLOOKUP(B2,director!A:B,2,0)),"UNKNOWN")

Cheers
Sergio

 

[jconkl02]

Hi J
That is quite different, in this case, the case you describe in your second mail, you do not need a macro/VBA you just need a formula in A2

=IFERROR(IF(RIGHT(B2,2)="RN",VLOOKUP(C2,market!A:B,2,0),VLOOKUP(B2,director!A:B,2,0)),"UNKNOWN")

Cheers
Sergio

Sergio,

My apologies, I rushed that last email because I was trying to leave the office. If I only needed to populate A2 or if the number of lines in the worksheet "Data" was static, then your formula would be perfect. However, in my haste I left out that I would need to populate every cell in column A if there were values in column B and the number of rows will vary daily.

BR,
Jason

 

[sergioMabres]

Hi Jason
So you do need a macro/vba to write the formula in column A from A2 to as far down as columns B has values, using FormulaR1C1 and end xldown and such
Cheers
Sergio