Help with loop and MID function

[Leone]

Hello.
I am new here.


I'm learning to work with vba and I'm having some difficulty.
I have a column with several cells in the following format: "001-090621 -001". I need to extract the values "090621" into one column and into another column the values "001".


Regarding logic, I believe that the best option would be to create a loop that checks the fields that have data and extract the values of each cell, however, I'm trying to learn how to do this, but I'm having difficulties in learning.


Below is the example:

28833


Thanks.

[JKwan]

I would use SPLIT function, this should give you a hand
If you want more help, just ask more

[anish.ms]

Sub SplitNum()
Dim myCell As Range, myRange As Range
Dim lastRow As Long
Dim Tmp As Variant
lastRow = ActiveSheet.UsedRange.Rows.Count
Set myRange = ActiveSheet.Range("A2:A" & lastRow)

For Each myCell In myRange
If Not IsEmpty(myCell) Then
Tmp = Split(myCell, "-")
ActiveSheet.Cells(myCell.Row, 2) = Tmp(1)
ActiveSheet.Cells(myCell.Row, 3) = Tmp(2)
End If
Next myCell
End Sub

[arnelgp]

copy this in a Module (VBA):

Public Function fnkSplit(ByVal the_value As Variant, ByVal the_delimiter As String, ByVal part_num As Integer) As Variant
Dim var
fnkSplit = vbNullString
var = Split(the_value, the_delimiter)
If UBound(var) + 1 >= part_num Then
fnkSplit = var(part_num - 1)
End If
End Function

if "Prf Codigo Parcelas" is in Column A, then type
below "Codigo":

=fnkSplit(A2,"-",2)

and on Adjacent cell:

=fnkSplit(A2,"-",3)

//copy the formula down.

[anish.ms]

Good one

[Leone]

Sub SplitNum()
Dim myCell As Range, myRange As Range
Dim lastRow As Long
Dim Tmp As Variant
lastRow = ActiveSheet.UsedRange.Rows.Count
Set myRange = ActiveSheet.Range("A2:A" & lastRow)

For Each myCell In myRange
If Not IsEmpty(myCell) Then
Tmp = Split(myCell, "-")
ActiveSheet.Cells(myCell.Row, 2) = Tmp(1)
ActiveSheet.Cells(myCell.Row, 3) = Tmp(2)
End If
Next myCell
End Sub




hello, it helped me a lot.
I just added: " "'" & " after "ActiveSheet.Cells(myCell.Row.." to force excel to show zeros