0

I'm trying to clean up my recorded Macro to make it less prone to error when I run it in future months (I'm trying to automate my monthly reporting). I have heard references to Activecell and the like can be dangerous so I'm looking to sure it up.

Outline of requirement. I'm using multiple formulas to grab contents from another worksheet and fill down to the end of said data on the other worksheet.

Sub AnswesComboFill()
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=EvalTableData!RC[2]"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=EvalTableData!RC[4]"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=EvalTableData!RC[1]"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(EvalTableData!RC[-3],AnswersData!C[-3]:C[2],6)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(EvalTableData!RC[-4],AnswersData!C[2]:C[7],6)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(EvalTableData!RC[-5],AnswersData!C[7]:C[12],6)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(EvalTableData!RC[-6],AnswersData!C[12]:C[17],6)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(EvalTableData!RC[-7],AnswersData!C[17]:C[22],6)"
    Range("A2:H2").Select
    Selection.AutoFill Destination:=Range("A2:H2000"), Type:=xlFillDefault
    Range("A2:H2000").Select
    Selection.NumberFormat = "General"
End Sub

Questions:

Can I get some direction for changing it so the cell references are static?

Some of the cells I'm referring to are formatted as Text, hence the last line to format to General. Even though I change the format to general the contents still stay as text (for example A2 will just say "=EvalTableData!A2" and not the contents). Is there a way to refresh the contents after I've changed the format?

I would like to the fill down range to be dynamic and not A2:H2000 like in the formula. I currently have a range.lastcell style formula in one of my other macros but it doesn't work in this case because I'm referring to a different worksheet. Can I get some assistance with that too?

Community
  • 1
  • 1
Tim
  • 25
  • 6
  • Have you tried recording with/without the `Use Relative References`button enabled? (just below the `Record Macro` button.) – ashleedawg Apr 16 '18 at 06:35
  • Also have a look at [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Apr 16 '18 at 06:41
  • Thanks heaps guys. Some good fundamentals there. – Tim Apr 16 '18 at 11:41

1 Answers1

0

Instead of 

Range("A2").Select
ActiveCell.FormulaR1C1 = "=EvalTableData!RC[2]"

Use

Range("A2").FormulaR1C1 = "=EvalTableData!RC[2]"

To make this range dynamic

Range("A2:H2000").Select
Selection.NumberFormat = "General"

make it 

Range(Range("A2"),Range("H2").end(xldown)).NumberFormat = "General"

To fix the text issue the formulae should be corrected to

ActiveCell.FormulaR1C1 = "='EvalTableData'!RC[2]"
Raunak Thomas
  • 1,393
  • 1
  • 12
  • 28
  • Thankyou! This is helpful. I know it's a simple question but I'm so new to this that I didn't know the terminology to search for to find an answer that worked for me. – Tim Apr 16 '18 at 11:41
  • I think this formula is going to work for me but I'm having issues with the formatting of various cells. Both my lookup cell and the range of cells it's lookup up are formatted as general but they are formatted differently. One of them has the date that looks like a date, the other has the date formatted as a number string. This is causing the VLOOKUP to break (I ammended the formulas you listed with a ",FALSE" at the end) for example my lookup value is "BobSmith10/05/2018" but the same value appears as "BobSmith43182.52162" in my lookup array. Cont: next comment – Tim Apr 16 '18 at 12:29
  • Is there a line I can insert into the macro that returns BobSmith10/05/2018 to fix this? (It's currently a "=CONCATENATE" formula – Tim Apr 16 '18 at 12:32
  • No, because the macro just changes the value in the formulae bar. Its the same as typing out the formulae yourself, just that RC[2] is a convention used to dynamically assign the cell range. You should use the `Date` or `datevalue` excel formulae in the lookup itself – Raunak Thomas Apr 16 '18 at 12:39