Thread: Adding a function to this macro:
View Single Post
 
Old 12-17-2017, 05:37 AM
JohnGanymede JohnGanymede is offline Windows 7 64bit Office 2010 64bit
Novice
  
Join Date: Jul 2017
Posts: 9
JohnGanymede is on a distinguished road
Default Adding a function to this macro:
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
Reply With Quote