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

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