Adding a function to this macro:

NoSparks · #5 12-20-2017, 07:45 AM

I'm no pro either... just an old retired guy that plays with this stuff for entertainment when the trout aren't biting

Does this work? I'm guessing that by Null values you mean blank cells.

Code:

Dim rng As Range, cl As Range

Application.ScreenUpdating = False

With ActiveSheet
    Set rng = .Range("C5:C14")
    If WorksheetFunction.CountA(rng) = 0 Then Exit Sub
    .Unprotect Password:="jtls"
    For Each cl In rng
        cl.Resize(, 3) = Split(cl.Value, ";")
    Next cl
    For Each cl In rng.Resize(, 3)
        If cl = "" Then cl = 0
    Next cl
    .Protect Password:="jtls"
End With

Application.ScreenUpdating = True

Still not sure I understand things.
Don't know the layout of your sheet. Could be that there's 'stuff' below line 14 and that pasting in a csv of more than 10 rows would just screw things up.
I can limit things to 3 columns with code like above but have no idea as to how many lines the originating csv data is.

To my line of thinking, the most idiot proof way would be to paste the original csv data to a blank sheet, manipulate it there, text to columns (or adapt above), count of rows, etc. and deal with it from there, eventually copying the required 10 row by 3 column array to the C5:E14 target range.

Code:

.Range("C5:E14").value = SomeOtherSheet.Range("A1").resize(10,3).value

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Adding to a macro	Ulodesk	Word VBA	4	03-07-2016 01:40 PM
Sum function not adding correctly	rs456a@att.com	Excel	4	05-28-2015 11:48 PM
Compiling/Indexing hundreds of recipes + adding a search by ingredient function.	batarcit	Excel	2	08-13-2014 03:09 PM
How do you write a macro with an IF Function	Scheuerman1987	Excel	5	06-05-2013 06:04 AM
Help! for using an appropriate function/Macro in Excel	pawan2paw	Excel	1	06-04-2009 12:28 PM