none
If Cell empty enter formula. RRS feed

  • Question

  •     I have this code: 
        With wsA.Range("M" & LastRow)
    
    		.Formula ==IFERROR(INDEX('NCMR Data'!A3:A99999,MATCH(1,INDEX(('NCMR Data'!$C3:$C99999=$F3)*('NCMR Data'!$D3:$D99999=$D3),0),0)),"")
    
            End With

    Beyond making it work, what I'd like to do is instead of replacing all cells, I'd like to replace only the cells that are empty. For there may be data in M, this, so what I'm afraid will happen here is that if a cell has a number in it, the script will stop working.

    Can someone here please help?


    • Edited by Matt Ridge Monday, May 7, 2012 1:19 PM
    Monday, May 7, 2012 1:19 PM

Answers

  • The formula should be a string enclosed in double quotes, and double quotes within the formula should be doubled:

    .Formula ="=IFERROR(INDEX('NCMR Data'!A3:A99999, MATCH(1, INDEX(('NCMR Data'!$C3:$C99999=$F3)*('NCMR Data'!$D3:$D99999=$D3), 0), 0)), """")"

    Your code as is will assign this formula to the last filled cell in column M only (assuming that that's what LastRow signifies). From your description I get the impression that you want to assign it to multiple cells. Can you explain?


    Regards, Hans Vogelaar

    Ok, here, let me show you the script I'm working with. 

    Option Explicit
    
    Sub tgr()
        
        Dim wsB As Worksheet 'BackOrder
        Dim wsJ As Worksheet 'Jobs List
        Dim wsA As Worksheet 'Archive
        Dim LastRow As Long
        
        Set wsB = Sheets("BackOrder")
        Set wsJ = Sheets("Jobs List")
        Set wsA = Sheets("Archive")
        
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
    
        With Intersect(wsJ.UsedRange, wsJ.Columns("Q"))
            .AutoFilter 1, "<>Same"
            With Intersect(.Offset(2).EntireRow, .Parent.Range("B:N"))
                .Copy wsA.Cells(Rows.Count, "B").End(xlUp).Offset(1)
                .EntireRow.Delete
            End With
            .AutoFilter
        End With
        
        LastRow = wsB.Range("B6").End(xlDown).Row
        wsB.Range("P5:Q5").Copy wsB.Range("P6:Q" & LastRow)
        Calculate
        wsB.UsedRange.Copy Sheets.Add.Range("A1")
        
        With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("Q"))
            .AutoFilter 1, "<>Different"
            .EntireRow.Delete
            With .Parent
                .AutoFilterMode = False
                Intersect(.UsedRange, .Columns("G")).Cut .Range("F1")
                Intersect(.UsedRange, .Columns("H")).Cut .Range("G1")
                Intersect(.UsedRange, .Columns("L")).Cut .Range("H1")
                Intersect(.UsedRange, .Columns("N")).Cut .Range("I1")
    
                Intersect(.UsedRange, .Range("B:I")).Copy wsJ.Cells(Rows.Count, "B").End(xlUp).Offset(1)
                .Delete
            End With
        End With
        
        LastRow = wsJ.Cells(Rows.Count, "B").End(xlUp).Row
        wsJ.Range("R1:Y1").Copy
        wsJ.Range("B3:I" & LastRow).PasteSpecial xlPasteFormats
        wsJ.Range("P1:Q1").Copy wsJ.Range("P3:Q" & LastRow)
        wsJ.Range("Z1:AC1").Copy wsJ.Range("J3:M" & LastRow)
        wsJ.Range("N3:N" & LastRow).Borders.Weight = xlThin
        With Application
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
    
    End Sub
    

    What I want to do is to add that script into this code, so when the lines go into archive the script you commented on would be put into M, so that if there is a number associated with it, the code will automatically update. 

    I hope this makes sense?

    • Marked as answer by Matt Ridge Friday, June 1, 2012 12:47 PM
    Monday, May 7, 2012 4:25 PM

All replies

  • The formula should be a string enclosed in double quotes, and double quotes within the formula should be doubled:

    .Formula ="=IFERROR(INDEX('NCMR Data'!A3:A99999, MATCH(1, INDEX(('NCMR Data'!$C3:$C99999=$F3)*('NCMR Data'!$D3:$D99999=$D3), 0), 0)), """")"

    Your code as is will assign this formula to the last filled cell in column M only (assuming that that's what LastRow signifies). From your description I get the impression that you want to assign it to multiple cells. Can you explain?


    Regards, Hans Vogelaar

    Monday, May 7, 2012 2:18 PM
  • The formula should be a string enclosed in double quotes, and double quotes within the formula should be doubled:

    .Formula ="=IFERROR(INDEX('NCMR Data'!A3:A99999, MATCH(1, INDEX(('NCMR Data'!$C3:$C99999=$F3)*('NCMR Data'!$D3:$D99999=$D3), 0), 0)), """")"

    Your code as is will assign this formula to the last filled cell in column M only (assuming that that's what LastRow signifies). From your description I get the impression that you want to assign it to multiple cells. Can you explain?


    Regards, Hans Vogelaar

    Ok, here, let me show you the script I'm working with. 

    Option Explicit
    
    Sub tgr()
        
        Dim wsB As Worksheet 'BackOrder
        Dim wsJ As Worksheet 'Jobs List
        Dim wsA As Worksheet 'Archive
        Dim LastRow As Long
        
        Set wsB = Sheets("BackOrder")
        Set wsJ = Sheets("Jobs List")
        Set wsA = Sheets("Archive")
        
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
    
        With Intersect(wsJ.UsedRange, wsJ.Columns("Q"))
            .AutoFilter 1, "<>Same"
            With Intersect(.Offset(2).EntireRow, .Parent.Range("B:N"))
                .Copy wsA.Cells(Rows.Count, "B").End(xlUp).Offset(1)
                .EntireRow.Delete
            End With
            .AutoFilter
        End With
        
        LastRow = wsB.Range("B6").End(xlDown).Row
        wsB.Range("P5:Q5").Copy wsB.Range("P6:Q" & LastRow)
        Calculate
        wsB.UsedRange.Copy Sheets.Add.Range("A1")
        
        With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("Q"))
            .AutoFilter 1, "<>Different"
            .EntireRow.Delete
            With .Parent
                .AutoFilterMode = False
                Intersect(.UsedRange, .Columns("G")).Cut .Range("F1")
                Intersect(.UsedRange, .Columns("H")).Cut .Range("G1")
                Intersect(.UsedRange, .Columns("L")).Cut .Range("H1")
                Intersect(.UsedRange, .Columns("N")).Cut .Range("I1")
    
                Intersect(.UsedRange, .Range("B:I")).Copy wsJ.Cells(Rows.Count, "B").End(xlUp).Offset(1)
                .Delete
            End With
        End With
        
        LastRow = wsJ.Cells(Rows.Count, "B").End(xlUp).Row
        wsJ.Range("R1:Y1").Copy
        wsJ.Range("B3:I" & LastRow).PasteSpecial xlPasteFormats
        wsJ.Range("P1:Q1").Copy wsJ.Range("P3:Q" & LastRow)
        wsJ.Range("Z1:AC1").Copy wsJ.Range("J3:M" & LastRow)
        wsJ.Range("N3:N" & LastRow).Borders.Weight = xlThin
        With Application
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .DisplayAlerts = True
            .ScreenUpdating = True
        End With
    
    End Sub
    

    What I want to do is to add that script into this code, so when the lines go into archive the script you commented on would be put into M, so that if there is a number associated with it, the code will automatically update. 

    I hope this makes sense?

    • Marked as answer by Matt Ridge Friday, June 1, 2012 12:47 PM
    Monday, May 7, 2012 4:25 PM
  • I have trouble visualizing where the line of code should go.


    Regards, Hans Vogelaar

    Monday, May 7, 2012 8:42 PM