Now that I look at your worksheet I see I misunderstood your request. This is one reason I grouse about A1 format: A poster says "A2", and I can't tell whether he means that row particularly or just "the current row". We need a convention like "Ar" (or something) to mean "the current row, column A".
R1C1 is better. But I don't really hope, realistically speaking, to get everyone used to saying "RC1" instead of "$Ar". It is much less ambiguous, though.
Back to your question: Now that I see the actual formulae over in column O, I realize what you're trying to do. In that case, I'm still sure you can do it with VBA and "remote" formulae—to coin a term—but it's easier this way: In column K try "=LEFT(RIGHT(A2,9),5)" (without the quotes). You don't need to check the length of the name in column A; all you need, unless there's more variation in the filenames than shows here, is to take the rightmost nine characters of the filename, and then the leftmost five characters of the result. If you need the results to be displayed as a number instead of a character string, just add 0 ("=LEFT(RIGHT(A2,9),5)+0"). Copy that formula down col K and see whether it isn't what you want.
Do you still want to do it in VBA, or at least to know how? If so, we can work on that too. It would be useful, if some filenames differ from the pattern I see here.
|