VBA help - find string between 2nd and 3rd occurrence of a character.

[vavs]

I am trying to replicate the following formula in a VBA script. I have created a few variables to allow me to loop through the cells properly.



I have one variable varIteration which is simply a counter of how many loops I make. I need to loop 6 times.

I also have a varColon which uses the following code

varColon = (Len(Range("E" & currentrow)) - Len(Replace(Range("E" & currentrow), ";", "")))

This will count the number of occurrences of the ";" in the cell. If the iteration variable is greater than the colon variable, I am just going to put a null string in my cell.



Here is what I have in Excel and it is operating properly.

=MID(E2,(FIND(";",E2)+1),(FIND(CHAR(1),SUBSTITUTE(E2,";",CHAR(1),2)) - FIND(";",E2)-1))

Here is what I put in VBA

Q1 = Mid(Range("E" & currentrow), WorksheetFunction.Find(char(1), Substitute("E" & currentrow, ";", char(1), varIteration)), WorksheetFunction.Find(";", Range("E" & currentrow)) - 1)

Q1 is a variable for the first question.



The context here is I have a worksheet with a number of responses to questions. some of the columns only have one answer and some of the columns have multiple answers separated by ";". I need to go through the sheet and pull the first 5 columns every time and then the first answer to each of the questions. Once I have that I need to repeat for the second, third ... answers. If there are only two answers, when I get to the third answer for a question, I will put nothing in the variable to indicate there was no answer.



I have 700 rows and a maximum of 6 answers to any question.



Any assistance would be greatly appreciated.

 

[DanteAmor]

Any assistance would be greatly appreciated.

You could put 5 or 6 examples and the result you expect in the cells.
It would also be great if you explained each example of what you have in the cell and what you expect as the result.

Put your examples using the XL2BB tool, that way, it seems to me, you will receive more help.

 

[vavs]

I found another method to get the string using the array function

arrParts = Split(Range("E" & currentrow).Value, ";")

I created a variable based on arrParts(the occurrence of the ";" in the string)

This actually works very well.

 

[DanteAmor]

Tip.

With Split you can obtain the parts of a text.

And in the following way you can obtain a specific string, for example if you want the first one:

  s1 = Split(Range("E" & currentrow).Value, ";")(0)

If you want the second:

  s2 = Split(Range("E" & currentrow).Value, ";")(1)

If you want the third:

  s3 = Split(Range("E" & currentrow).Value, ";")(2)