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
1	Ticket Owner	Queue	Wrls_Mkt
2		FOTORN	Toledo : Toledo
3		V1ERRN	Miami / West Palm : Miami
4		V1ALRN	Washington DC : Washington DC
5		V1SARN	Miami / West Palm : Non-MSA
6		V1SARN	DFW : Fort Worth / Arlington
7		FOTORN	DFW : Fort Worth / Arlington
8		V1AL	West Washington : Seattle / Bellevue / Everett
9		FOTORN	Pittsburgh : Sharon
10		V1ERRN	Tampa : Tampa / St. Petersburg / Clearwater
11		V1ALRN	DFW : Fort Worth / Arlington
12		V1SARN	Upper Central Valley : Modesto
13		V1SARN	Upper Central Valley : Modesto
14		FOTORN	Lower Central Valley : Bakersfield
15		V1AL	West Texas : Non-MSA
16		FOTORN	Myrtle Beach : Wilmington, NC
17		V1ERRN	Myrtle Beach : Wilmington, NC
18		V1ALRN	Memphis : Memphis
19		V1SARN	Baltimore : Baltimore
20		V1SARN	Chicago : Chicago
21		FOTORN	North Wisconsin : Non-MSA
22		V1AL	Myrtle Beach : Jacksonville, NC
23		V1AL	New York City : New York
24		FOTORN	West Texas : Non-MSA
25		FOTORN	Ft. Wayne / South Bend : Fort Wayne

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Excel 2010

	A	B
1	Market	Director
2	Baltimore : Baltimore	Peter Pan
3	Baltimore : Non-MSA	Peter Pan
4	Boston : Barnstable / Yarmouth	Peter Pan
5	Boston : Boston	Peter Pan
6	Idaho : Boise City	Captain Hook
7	Inland Northwest : Non-MSA	Captain Hook
8	Inland Northwest : Richland / Kennewick / Pasco	Captain Hook
9	Inland Northwest : Spokane	Captain Hook
10	Cincinnati : Hamilton / Middletown	Tinker Bell
11	Cincinnati : Lima	Tinker Bell
12	Cincinnati : Non-MSA	Tinker Bell
13	Cleveland : Akron	Tinker Bell
14	West Kentucky : Non-MSA	Wendy Darling
15	West Kentucky : St. Louis	Wendy Darling
16	Alabama : Anniston	Wendy Darling
17	Alabama : Birmingham	Wendy Darling
18	Alabama : Decatur, AL	Wendy Darling
19	Arkansas : Fayetteville / Springdale / Rogers	Tiger Lily
20	Arkansas : Fort Smith	Tiger Lily
21	Arkansas : Little Rock / North Little Rock	Tiger Lily
22	Arkansas : Non-MSA	Tiger Lily

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Market​

Excel 2010

	A	B	C
1	Queue	Director	Group
2	PROI	Mr. Smee	SACO
3	RSAN	George Scourie	OEM
4	ECPT	Black Gilmour	SACO
5	MOT2	Canary Robb	SACO
6	T2LE	Bill Jukes	SACO
7	VBO2	Black Pirate	SACO
8	VBSC	Alf Mason	SACO
9	WBHE	Robert Mullins	SACO
10	WBHSCB	George Scourie	SACO

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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