Small VBA code to split cells if...

[Lolo1313]

Hello everyone

A noob needs your help

Im trying to make the following happen:

Process 1: An certain array (length and contained values are variable) containing strings is being filtered out in column I, whenever i change the value of a drop down list located in the cell D2.
Process 2: In the column K are the i-th indexes (whereby i = value of column J) of the array in column I.
Process 3: The strings from column K need to be split in column L & M. I wish to split them at the first "." found in the string, whereby if cell k is empty, then return blank cells in columns L & M of the corresponding cell k. Furthermore, if the string of column K doesn't have a ".", then return the original value in column L.
Process 4: When the value of D2 changes again, the column K, L & M update automatically

An example of my sheet can be found in the picture below:

Processes 1 & 2 are straight forward and work pretty well with basic cell functions.
In order to make process 3 happen, I've gathered a few vba codes found on the net an modified them to fit my needs. But, am not able to compile it.

Since my vba knowledge is quite limited, I hoped to get some help from mrexcel.
Here is the code I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$2" Then
        Call Split_Cells
    End If
End Sub


Sub Split_Cells()

    Dim rngMyCell As Range
    Dim strSplitVals() As String
    
    For Each rngMyCell In ActiveSheet.Range("K3:K" & ActiveSheet.Range("K" & ActiveSheet.Rows.Count).End(xlUp).Row)
        If ISBLANK(rngMyCell) = False Then
            If InStr(rngMyCell, ".") > 0 Then
                strSplitVals = Split(rngMyCell.Value, ".")
                rngMyCell.Offset(0, 1) = strSplitVals(0)
                rngMyCell.Offset(0, 2) = strSplitVals(1)
            End If
            
        ElseIf ISBLANK(rngMyCell) = True Then
            ActiveCell.Offset(0, 1).Clear
            ActiveCell.Offset(0, 2).Clear
        End If
        
        Next rngMyCell

End Sub

Thank you in Advance for your help!

 

[Joe4]

Welcome to the Board!

Why do you need VBA?
Columns L and M can be calculated pretty easily with formulas (which are dynamic, and change automatically when the source data changes).

So, if you have a value in K3, here is what the formulas would look like:
L3: =IFERROR(LEFT(K3,FIND(".",K3)-1),K3)
M3: =IFERROR(MID(K3,FIND(".",K3)+1,LEN(K3)),"")
and then just copy down for all rows.

If you may be adding data and want these formulas to populate automatically, you can just use VBA to copy down the formulas in columns L and M as far down as you need.

 

[Lolo1313]

Hi Joe

Thanks for your reply!

Indeed, I should have made it easier...
Your solution worked perfectly, thanks!

 

[Joe4]

You are welcome.
Glad it worked out for you!