Small VBA code to split cells if...

Lolo1313

New Member
Joined
Jun 2, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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:

excelv1.JPG


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:

VBA Code:
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,153
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Lolo1313

New Member
Joined
Jun 2, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hi Joe

Thanks for your reply!

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,153
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad it worked out for you!
:)
 

Forum statistics

Threads
1,148,363
Messages
5,746,268
Members
424,002
Latest member
anon341

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top