I know how to use the function InStr, but what I don't know is how to use it this way:
Image of my excel. I want to make a loop to insert the data in the right cells. My code to add the 2nd element is:
For j = 3 To 129
For i = 2 To 849
Cells(i, j) = Mid(Cells(i, 1), InStr(Cells(i, 1), ",") + 1, (InStr(InStr(Cells(i, 1), ",") + 1, Cells(i, 1), ",")) - (InStr(Cells(i, 1), ",") + 1))
Next i
Next j
The problem is, I would have to use a complicated loop to do a lot of InStr depending on the variable j.
If the function InStr had a place where I say: I wanna find the xth character (",") I would make it work; the problem is that I need a double InStr to ignore the 1st "," so it gives me the position of the 2nd ",".
I think the question is a lot more complicated, so ask me anything you didn't understand!
P.S.: I already inserted the values of the column B/entry and C/class.
Use Split() instead:
If you want to put all the values in the same order that they already appear in:
Dim values As Variant
For i = 2 to Cells(Rows.Count, 1).End(xlUp).Row
values = Split(Cells(i, 1).Value, ",")
Cells(i, 1).Resize(1, UBound(values) + 1).Value = values
Next
Or if you want different values in different columns:
Dim values As Variant
For i = 2 to Cells(Rows.Count, 1).End(xlUp).Row
'// Create an array of values from your string
values = Split(Cells(i, 1).Value, ",")
'// Lets say you want to put the 3rd value in column D
Range("D" & i).Value = CStr(values(2)) '// arrays are zero indexed and start at 0
Next