vba to truncate vlookup string at pipe symbol - error 1004

[sxschech]

Quotes are confusing, even after looking at various examples, didn't seem to get it working until now. Hope is useful...

Using vlookup to get a title from another sheet. This is working. I have put into vba so that when a new csv file is ready, it will reformat to excel and insert the formula:

[tt]
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],[SurveyFilterMacro_NoTitle.xlsm]Titles!R3C3:R949C4,2,FALSE)"
[/tt]

To refine it, would like to remove all text after the pipe | symbol. No problem manually adjusting the formula in the cells, however, in VBA seems to have a problem with the pipe and quotes. For whatever reason multiple quotes still seemed to get an error. I also tried using Chr(124) and no luck. Since I tried doubling the quotes directly in the formula and it didn't work, I didn't think it would work as a standalone variable, however, turned out that in the variable the extra double quotes before and after chr(34) worked.

        stFind = "" & Chr(34) & " |" & Chr(34) & ""
        ActiveCell.FormulaR1C1 = _
        "=LEFT(VLOOKUP(RC[-3],[SurveyFilterMacro_NoTitle.xlsm]Titles!R3C3:R949C4,2,FALSE),FIND(" & stFind & ",VLOOKUP(RC[-3],[SurveyFilterMacro_NoTitle.xlsm]Titles!R3C3:R949C4,2,FALSE),1) - 1)"

If there is a better way to write, I'm open to recommendations.

 

[SkipVought]

Please post an example of a string in the range you are looking up.

Is there any reason why you could not use a formula to parse out the segment you are interested in to the right of the table? Then use INDEX() and MATCH() rather than VLOOKUP().

Furthermore, why the VBA? I can't remember the last time I coded a formula for a table in a sheet. Are you designing a sheet entirely from code?

There may be other alternatives. Are you open to that?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

Hi Skip,

I tried index and match, but obviously got the formula wrong, and since was able to get a result with vlookup, went with that. Be happy to change if the other way is better. Regarding the Titles, newer and current classes use pipe, some older classes use a dash, but felt trying to deal with a few old classes would get complicated since many current classes also use a dash as a hyphen rather than a separator between the description and the code, so wouldn't be able to always rely on it. In the example below looking to "fill in" the Title in col H by locating the associated Title_Code in Col G of File2 and matching to Cols C and D of File1. File2 is formatted as an excel table after conversion from csv.

Sample data:

File1 (Titles is on Tab2)

A      B        C            D                                  E          F         G
ID     Group    Title_Code   Title                              More1...   More2..., etc
-------------------------------------------------------------------------------------------
AQ4    Live     [COLOR=#CC0000]AXVOD15[/color]      [COLOR=#EF2929][b]Beginning Programming[/b] | AXVOD15[/color]    12/01/2015  ...        ...
AQ7    Web      AXAOL15      Beginning Data Entry | AXAOL15     12/01/2015
...    ...      .......      Another Course - BDOD13            05/18/2013

File2 (Weekly Contains a Single Tab)
A      B        C           D                 E        F              G               H                           
ID     EVENT    ATTENDED    START_DATE        PASSED   Email          TITLE_CODE      {*** Added by VBA formula vlookup or index/match
--------------------------------------------------------------------------------      ----------------------------
RBG1  562349    1           12/10/2015        1        brad@.com      [COLOR=#EF2929]AXVOD15[/color]         [b]Beginning Programming[/b]
WEX5  562349    1           12/10/2015        0        lisa@.com      AXVOD15         Beginning Programming
QZB8  562100    0           12/18/2015        0        joe@.gov       AXAOL15         Beginning Data Entry

 

[SkipVought]

...in addition to my previous post...

...when a new csv file is ready...

So what I have most often done is manually add a QueryTable to import a .csv text file. So I'd have the necessary formula coded in the column directly to the right of the resultset. When "a new .csv file is ready," you merely need to refresh the QT, and the formula will adjust to the new row count. No VBA required for this part of the process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

I'm doing a bunch of other things to/with the data which is why using vba, and because it is formatted as a table, it auto populates all the rows. I would like to explore queries in excel when I get some time and see how that works. The couple times I tried queries, seemed that access was easier to work with than excel.

 

[SkipVought]

Well if yer gonna use VBA, why use a spreadsheet formula? Why not a UDF?

Function GetTitle(rng As Range)
   Dim a
   a = Split(rng, "|")

   If UBound(a) > 0 Then
      GetTitle = Trim(a(0))
   Else
      GetTitle = Trim(Split(rng, "-")(0))
   End If
End Function

Use this function just as you would any spreadsheet function. It will parse either | & -.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

Hi Skip,

To answer your question, I was using a formula because I only need to insert it in the first cell and it automatically populates the entire sheet/table so I don't need to do anything further. As I mentioned, the dash is more complicated since it does not always represent a delimiter, so can't parse out for that without further logic and at this point probably would take more time to build the logic then to deal with those few items as exceptions. I'll keep your code in mind when I need to revise the program. I could probably make use of that in Access. Is there a benefit to ubound over len as I usually do If Len(fieldname) > 0 or is that an excel thing.

 

[SkipVought]

You can use a UDF just like a spreadsheet formula!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]