VBA how to select specific characters from a string

[ivavok]

Hello,

could somebody help me with this please? In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this code:

For counter = 0 To not_empty_cells

For counter_dep = 1 To 5

position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))

If position > 0 Then
symbol_dep = Mid(Cells(counter + 3, 4), position, 2)

Cells(counter + 3, 10).Value = symbol_dep
End If

Next counter_dep

Next counter

It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.

Thank you

 

[ivavok]

For counter = 0 To not_empty_cells

Cells(counter + 3, 4).Offset(counter + 3, 6).Value = get_element(Cells(counter + 3, 4).Value)

Next counter

 

[Richard Schollar]

Full code means literally all your code you are using - so I wish to see what you are using for the get_elemnt code, also how the counter value has been populated.

The exact code that I had in my post for get_element (ie if you copy & pasted it into a code module) should have worked.

 

[ivavok]

Ok

Public Sub modifying_table()
Dim row1 As Long
Dim column1 As Long
Dim not_empty_cells As Long
Dim counter As Long

row1 = 3
column1 = 5
not_empty_cells = 0

Do While Not IsEmpty(ActiveSheet.Cells(row1, column1))
row1 = row1 + 1
not_empty_cells = not_empty_cells + 1
Loop

For counter = 0 To not_empty_cells

Cells(counter + 3, 4).Offset(counter + 3, 6).Value = get_element(Cells(counter + 3, 4).Value)

Next counter

End sub

 

[Richard Schollar]

Hi

Try the following. Copy everything into your code module (omit nothing):

Public Sub modifying_table()
Dim cell As Range
Dim rngWhole As Range
Set rngWhole = Range("D3:D" & Cells(Rows.Count, "E").End(xlUp).Row)
For Each cell In rngWhole
    cell.Offset(0, 6).Value = get_element(cell.Value)
Next cell
End Sub
Function get_element(ByVal s As String) As String
Static num_list
Dim i As Long
If IsEmpty(num_list) Then _
    num_list = VBA.Array(11, 20, 30, 60, 61)   'list of numbers to find
    
For i = 0 To UBound(num_list)
    If InStr(1, "," & s & ",", "," & num_list(i) & ",") > 0 Then _
        get_element = num_list(i): Exit Function    'if one of our numbers found, return found number and exit the loop
Next i
get_element = "Not found!"   'if we get here, none of the list of numbers has been located
End Function

 

[ivavok]

it works now!thank you very much!