0

I have a button that calls many separate macros. Everything works as expected, but with 1 exception.

I need to input a formula in row 2 of four separate columns and have the formulas drag down to the last populated row. In order to achieve this "easily" I've added a macro that will select from A1:AK700 (there should never be more than 500 rows so I went to 700 to be safe) and create a table. Then the formulas are input (I do the table so that the formulas automatically go down to the last populated row). I then have a macro to select the same range and convert it to range. Finally, I have a macro that runs after all this to copy and paste values and then take two of the tabs and create a new file with copies of those tabs.

Everything runs correctly, but at some point after converting to range (or around that part of the process) Excel slows down a lot, stops responding for a while and then finally starts again. Once everything as run, I go to both the original file and the newly created file and the table formatting is applied to the entire sheet (all the way down to cell ~1,048,000!). Clearly, this is why it's taking so long and ceases to respond, right?

How can I stop this?

I'm pasting the code in the order that it runs from the table creation point. I don't have very much experience writing macros (about a month) so any help would be appreciated!! And all of this does need to be done with a macro. They want it all to happen with just one button click.

Code:

Sub CreateTable()

    Sheet1.ListObjects.Add(xlSrcRange, Range("A1:AK500"), , xlYes).Name = "Table1"

End Sub

Sub FormulaInput()

   Sheets("Activity - Hires").Select
   Range("AH2").Select
   ActiveCell.FormulaR1C1 = _
       "=INDEX(NHO_Global!C[-21],MATCH('Activity - Hires'!RC[-24],NHO_Global!C[-33],0))"
End Sub

Sub FormulaInput2()

    Sheets("Activity - Hires").Select
    Range("AI2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(ISERROR(RC[-1]),RC[-15]=""Contingent Worker""),""Contingent Worker"",IF(AND(ISERROR(RC[-1]),ISNUMBER(SEARCH(""(Terminated)"",RC[-33]))),""Terminated"",IF(AND(ISERROR(RC[-1]),RC[-34]=TODAY()),""Hired Today"",IF(ISERROR(RC[-1]),""Under Investigation"",""""))))"
End Sub

Sub FormulaInput3()
    Sheets("Activity - Hires").Select
    Range("AJ2").Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(RC[-2]),"""",IF(RC[-2]<0,RC[-17],""""))"
End Sub


Sub FormulaInput4()
    Sheets("Activity - Hires").Select
    Range("AK2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(RC[-3]),"""",IF(RC[-3]<-10,""Obtain Manager Email"",""""))"
End Sub

Sub ConvertToNumber()
 'This is so that the formulas work as the numbers in column J are used, but they're stored as text initially 

    Range("J:J").Select
    With Selection
    Selection.NumberFormat = "General"
    .Value = .Value
    End With

End Sub

Sub ConvertToRange()

    Sheet1.ListObjects("Table1").Unlist

End Sub

Sub CopyPasteValuesActivity()

    Worksheets("Activity - Hires").Select
    Worksheets("Activity - Hires").Range("A1:AK700").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


Sub CopyPasteValuesNHO()

    Worksheets("NHO_Global").Select
    Worksheets("NHO_Global").Range("A1:Q700").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub


Sub SaveSepFile()

    Sheets(Array("Activity - Hires", "NHO_Global")).Select
    Sheets("Activity - Hires").Activate
    Sheets(Array("Activity - Hires", "NHO_Global")).Copy

End Sub

As indicated in the comments, I decided to remove the table creation and removal processes completely. I figured out how to have the formulas input and dragged down to the last populated row so I just did that. That code is below (for just one of the formulas):

Sub FormulaInput12()

Dim rng As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("Activity - Hires")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Set rng = Range("AH2:AH" & LastRow)
rng.Formula = "=INDEX(NHO_Global!C[-21],MATCH('Activity - Hires'!RC[-24],NHO_Global!C[-33],0))"

End Sub

Doing this allows the file to process in just a few seconds and everything is correct.

  • Why mix A1 refs with R1C1 within a procedure ? – Mark Fitzgerald Dec 11 '18 at 14:16
  • @MarkFitzgerald I recorded that portion. That's just how it showed up (mostly). – Lenny Dec 11 '18 at 14:19
  • 1
    I decided to remove the table creation and removal processes completely and instead modified the FormulaInput macros to drag the formulas down. The whole file processes in a couple of seconds now. If anyone can figure out why the original code was creating a massive table when the range was specified, though, I'd love to know! – Lenny Dec 11 '18 at 15:10
  • @Lenny,, try once the regular Index with Range instead of R1 C1,, and I must say that better you combine all the Macros into one since are simple steps to follow. – Rajesh S Dec 12 '18 at 7:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.