Results 1 to 8 of 8

How to add if null function to macro?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-19-2016
    Location
    Alaska
    MS-Off Ver
    MS 2010, 2013, 2016
    Posts
    272

    How to add if null function to macro?

    I have the macro below that works perfect. I need to make an adjustment but not sure where or how. Currently the macro separates the data based on a cell value and only transfers certain columns to new sheets. Well I need to check and see if the cell in AN is null, if it is then it should bring over the value found on BG, if it’s not then bring over AN still.

    The attachment has how the data should look, what you see highlighted is from the cell in BG because AN was Null.

    Sub Wires()
    Dim lRow As Long, nrow As Long, i As Long, n As Long
    Dim rng As Range, Cell As Range
    Dim Arr As Variant
    Dim Ws As Worksheet
    On Error GoTo Errorcatch
    Application.ScreenUpdating = False
    Set Ws = ActiveWorkbook.ActiveSheet
    Worksheets.Add().Name = "Outgoing Wires": Worksheets.Add().Name = "Incoming Wires"
    With Sheets("Incoming Wires")
        .Range("B2:I2") = Array("Wire Date", "Amount", "Originator", "Orig. Bank", "Orig. Acct", "Country", "Currency", "Orig. City/State")
        .Range("C:C").NumberFormat = "#,##0.00"
        
    End With
    With Sheets("Outgoing Wires")
        .Range("B2:I2") = Array("Wire Date", "Amount", "Beneficiary", "Ben. Bank", "Ben. Acct", "Country", "Currency", "Ben. City/State")
        .Range("C:C").NumberFormat = "#,##0.00"
        
            
    End With
    With Ws
        With .UsedRange
            .Font.Name = "Calibri"
            .Font.Size = 10
            For Each Cell In Ws.Range("I:I,H:H,L:L,AN:AN,AU:AU,AY:AY").SpecialCells(xlConstants, xlTextValues)
            Cell.Value = WorksheetFunction.Proper(Cell.Value)
            
            Next
            
            lRow = .Rows.Count
                  
        End With
        For i = 2 To lRow
            If .Range("T" & i) = "I" Then
                Arr = Array(.Cells(i, 66), .Cells(i, 3), .Cells(i, 51), .Cells(i, 40), .Cells(i, 49), .Cells(i, 16), .Cells(i, 17), .Cells(i, 47))
                
                With Sheets("Incoming Wires")
                    nrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
                    .Cells(nrow, 2).Resize(1, UBound(Arr) + 1) = Arr
                    
                End With
    
            ElseIf .Range("T" & i) = "O" Then
                Arr = Array(.Cells(i, 66), .Cells(i, 3), .Cells(i, 9), .Cells(i, 8), .Cells(i, 13), .Cells(i, 16), .Cells(i, 17), .Cells(i, 10))
                
                With Sheets("Outgoing Wires")
                    nrow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
                    .Cells(nrow, 2).Resize(1, UBound(Arr) + 1) = Arr
                    
                End With
            End If
            
            With Worksheets("Incoming Wires").UsedRange
            .Font.Name = "Calibri"
            .Font.Size = 10
            .Columns.AutoFit
                                                 
            End With
              
            With Worksheets("Outgoing Wires").UsedRange
            .Font.Name = "Calibri"
            .Font.Size = 10
            .Columns.AutoFit
                                                    
            End With
                    
        Next i
    End With
    Application.ScreenUpdating = True
    Exit Sub
    Errorcatch:
    MsgBox Err.Description
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count rows with multiple null and not null criteria
    By carlostheba in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2014, 05:16 AM
  2. Blank function/Null function for missing dates
    By knowledgeiskey in forum Excel General
    Replies: 2
    Last Post: 05-21-2010, 05:39 PM
  3. INDEX and MATCH function, NULL value?
    By mabbutt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 07:29 AM
  4. Using IF function to display Null field
    By rockycj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2008, 11:59 AM
  5. Replies: 7
    Last Post: 07-27-2006, 07:15 PM
  6. [SOLVED] Make a field null using IF function
    By [email protected] in forum Excel General
    Replies: 7
    Last Post: 06-20-2006, 02:10 AM
  7. How do I get a Function to return Null?
    By Peter Chatterton in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2006, 05:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1