0

I use the same autofill code all my other macros. The difference with this macro is that I implemented an Input box earlier in the code for a different function. I think I am having an issue with what cell should be used to autofill the other cells.

I keep getting a "This operation will cause some merged cells to unmerge. Do you wish to continue?" message but I already unmerged the entire sheet earlier in the code. Then VBA gives me an error: "Run-time error '1004': AutoFill method of Range class failed."

The correct function should use the cell "ActiveCell.Offset(rowOffset:=2, columnoffset:=13).Activate" as the data to auto fill down the column.

Any help would be appreciated.

*Edited to include successful code

Sub Macro1()
'
' Macro to cut/paste input row and autofill formula in column N
'
Dim myValue As Variant
    myValue = InputBox("Select Row to Move", "Input", (""))
Dim sh As Worksheet, lastrow As Long
Set sh = ActiveSheet

   If myValue = "" Then Exit Sub
   
'Unmerge sheet'

Cells.Select
Selection.UnMerge

'Select Input row, cut/paste

Rows(myValue).Select
Selection.Cut

ActiveCell.Offset(rowoffset:=-1).Activate
Selection.Insert Shift:=xlDown

'select cell in column N to input formula

ActiveCell.Offset(rowoffset:=2, columnoffset:=13).Activate
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+RC[-2]-RC[-1]"
    
'autofill cell down to last used row

 lastrow = sh.Range("B" & Rows.Count).End(xlUp).Row
    Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(lastrow, ActiveCell.Column)).FormulaR1C1 = "=+RC[-2]-RC[-1]"
        
    
     
End Sub
deadxcell
  • 11
  • 4
  • 2
    You may want to read [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba); its tips also apply to avoiding using `ActiveCell`. – BigBen Mar 16 '21 at 15:41
  • 1
    Also, you can write a formula to a range in one go, w/o using `AutoFill`: `Range("N2:N" & lastrow).Formula = "=+RC[-2]-RC[-1]"`. – BigBen Mar 16 '21 at 15:41
  • I appreciate the advice with the link. I will read up. I am still new to VBA coding. – deadxcell Mar 16 '21 at 17:43
  • Per your second comment, this code is giving me a "Method 'Range' of object'_Global' failed" error (Run-time error '1004'). Am I missing something? – deadxcell Mar 16 '21 at 17:44
  • Did you keep the line where you found the lastrow, i.e. `lastrow = sh.Range("A" & Rows.Count).End(xlUp).Row`? – BigBen Mar 16 '21 at 17:46
  • Yes, that line is still in the code. – deadxcell Mar 16 '21 at 17:55
  • Maybe [edit] your question with the revised code you're using. – BigBen Mar 16 '21 at 17:56
  • I think the issue is that there is no data in N2? The data I am applying the formula to starts at N11. I got it to work with my original code but I have concerns that the first line of data that this needs to be applied to will change. – deadxcell Mar 16 '21 at 17:58
  • The code is the same as what is posted except the last line: `sh.Range("N11").autofill Destination:=sh.Range("N11:N" & lastrow)` – deadxcell Mar 16 '21 at 18:06
  • My comment said to *not* use `AutoFill`. – BigBen Mar 16 '21 at 18:07
  • Yes I know. When I tried your suggestion, it gave me an error. I would still like to explore the use of it. I just don't know why it is producing the error. I was also updating that I got my original code to work; again, still interested in how to get your method to work as it seems cleaner. – deadxcell Mar 16 '21 at 18:13
  • I edited the post. I believe it now includes your code but I was able to remove the specific cells to allow flexibility coming from the input box. It now applies the formula to cells below last active cell until the end of the data. Thank you for your help. – deadxcell Mar 16 '21 at 18:38

0 Answers0