# If Cell empty enter formula. • ### 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.

• Edited by Monday, May 7, 2012 1:19 PM
Monday, May 7, 2012 1:19 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
.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

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
.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 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
.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

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
.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 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