I am attempting to create a custom function that works much as a reverse table lookup. I must find a string value within text field in an existing data table. I currently have 47 different strings in at least 2 possible locations.
Table1 contains the report output. Two columns of the table are labeled "Notes" and "Comments"
Table2 contains three columns "String," "Location," and "Output"
- Where [String] is found within [Notes], the function should return [Output] in a column labeled [Part] on Table1.
- To do this, I have loaded Table2 into a two-dimensional variable array named strPartArray.
- I have established a loop for each row of Table2 (within the array) to be processed.
- My intention is to take the contents of "String" to search within "Location" (either Notes or Comments or some future column reference to Table1) until a match is found, at which time the loop exits and "Output" would be the result of the fuction.
I am attempting to write this, and have written the variable array to key off the size of Table2 so that as more criteria is added, any number of Locations can be used to find specific strings. It is for this reason that I do not add "Notes" and "Comments" to the input fields of my function.
What I need is some method of determining, via VBA, what =[Table1[Notes][#This Row]] or =[Table1[@Notes]] would return. I realize I could use either a R1C1 reference or Offset reference to get this as well; however, I am also trying to avoid that as this is based from a report from a system whose column count varies enough to have me doing maintainence on this code too often for comfort. By utilizing the Tables' named Columns, I can limit maintainence time in the future.
I hope I've been thorough and thank you for any assistance.