Adding a function to this macro:

JohnGanymede · #1 12-17-2017, 05:37 AM

Morning.

I'm sorting through the functions for a tool made in Excel 2010. This macro is tied to a button for pasting csv data. The button populates a specific array after pasting. I have a few of these macros/buttons at different places on the tool. Cells outside the target arrays are protected.

In this case, the target array is C5:E14. All target cells are formatted as numbers. What I'd like is to add a function after other operations in this macro, that will look at the array of cells ( C5:E14 ), find null values, and replace nulls with "0". Nulls are breaking some of the IF's in my formulas.

As a secondary request, I'd love to build in some way to refuse csv pasting with too many values for the intended array.

Thanks for any help, or any point in the right direction to figure this out.

Cheers.

Code:

Application.ScreenUpdating = False

With ActiveSheet
    If WorksheetFunction.CountA(.Range("C5:C14")) = 0 Then Exit Sub
    .Unprotect Password:="jtls"
    .Range("C5:C14").TextToColumns Destination:=.Range("C5"), _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=False
    .Protect Password:="jtls"
End With

Application.ScreenUpdating = True

End Sub

Logit · #2 12-17-2017, 10:39 AM

.
To check for the Null value :

https://stackoverflow.com/questions/...empty-with-vba

NoSparks · #3 12-18-2017, 08:58 AM

What exactly do you mean by this ?

Quote:

As a secondary request, I'd love to build in some way to refuse csv pasting with too many values for the intended array.

JohnGanymede · #4 12-20-2017, 05:42 AM

Quote:

Originally Posted by NoSparks

What exactly do you mean by this ?

The project is a calculator tool. I'm trying to idiot proof it for users. In the above macro I'd like to set the acceptable range for pasting csv data, in this case C5:E14, and if the paste is too many columns or too many rows, I'd like it to exit the sub. A rejection notice of some kind would be nice.

It's not critical, but would be great.

I'm still sorting out the suggested IfEmpty function. It looks promising. This is a hobby application. I'm no pro and just doing this in my spare time.

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

JohnGanymede · #6 12-22-2017, 11:29 AM

Its a user error if the pasted data is more than 3 columns by 10 rows. I'm trying to idiot proof the tool but i cant idiot proof the user. Yes, there are protected areas outside the intended array. That function was just a "wish list" item. I'll sort it out or live without it.

JohnGanymede · #7 12-22-2017, 02:57 PM

editing fake news

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