2

I have to replace each blank cell in the column BM with the data in this same row in column DN. I have written that: 

Columns("BM:BM").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=DN2"

But the "=DN2" ends like "=@'DN2'" in every cell. Which makes an error. Does someone know how could I get the cell in DN from the same row please?

Thank you very much in advance!

GSD
  • 1,252
  • 1
  • 10
  • 12
Benjamin Perry
  • 75
  • 6
  • 3
    Change `.FormulaR1C1` to `.Formula` – Scott Craner Feb 26 '20 at 14:43
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 26 '20 at 14:44
  • @ScottCraner - you might want to use an actual `.FormulaR1C1` here - using a hard-coded row as in `DN2` might not work. – BigBen Feb 26 '20 at 14:45
  • 2
    As @BigBen stated maybe change the formula to an actual `R1C1` format: `"=RC118"` the issue is that you are mixing R1C1 and A1 notation. – Scott Craner Feb 26 '20 at 14:48
  • @ScottCraner Why don't you post it as a valid answer? I think that is the correct solution to the problem. – Pᴇʜ Feb 26 '20 at 14:51
  • This should be @BigBen's answer not mine. – Scott Craner Feb 26 '20 at 14:52
  • @ScottCraner - go ahead and post :-) – BigBen Feb 26 '20 at 14:52
  • 1
    This is a lot to process on a whole column. You sure you need it all? Also, would you really need formulas, or rather values? In other words, are you going to **need** these formulas and therefor process data twice? – JvdV Feb 26 '20 at 14:53
  • 1
    @JvdV no `SpecialCells` will limit to `UsedRange` so it doesn't fill all the rows to the bottom. Give it a try. – Pᴇʜ Feb 26 '20 at 14:55
  • @Pᴇʜ If that is so (and I do believe you are right), this might be even more problematic. What if data in any other column actually **exceeds** (by far) the last used row in column BM? You'll end up with ton's of rows populated with formulas where you might not want them. I'd rather go with the last used row from column `DN` and take it from there if that's the case =) – JvdV Feb 26 '20 at 14:59
  • @JvdV well, that's true. Depends on how the rest of the data looks like. But limiting it to the last used row of DN might definitely be a solid solution. Good point. – Pᴇʜ Feb 26 '20 at 15:05

3 Answers3

4

If you are going to R1C1 then make the Formula R1C1

If Application.CountA(ActiveSheet.Range("BM:BM"))<> ActiveSheet.Rows.Count Then
    ActiveSheet.Range("BM:BM").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC118"
End If
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
4

Another way to do this

Dim rng As Range

On Error Resume Next
Set rng = Columns("BM:BM").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    rng.Formula = "=DN" & rng.Row
End If

BTW if you are using SpecialCells, use error handling else your code will crash when it doesn't find those cells

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
2

Here are my two cents. I would drop the idea of using SpecialCells on whole columns altogether. If you want to go with SpecialCells then atleast create a Range within the BM column first to use it on. If any column exceeds the rows of interest you'll end up with values in places you really don't want (formulas in your case).

Here is a small example spanning A1:C5:

enter image description here

Our area of interest is column B:B where, if empty, we need the values from column C:C

When we run code using SpecialCells, for example: 

Columns("B:B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC3"

We will end up with:

enter image description here

What we see is that SpecialCells makes use of UsedRange (as @Peh rightfully mentioned in the comment section) and therefor is highly unreliable when used like that. There is a risk of ending up with values/formulas where you don't want them. I suggest making use of the last used row from column C:C (or in your case DN) and go from there. So allready much saver would be something like:

.Range("BM1:BM" & <LastUsedRowOfDN>).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC118"
JvdV
  • 70,606
  • 8
  • 39
  • 70