Formula Autofill Debugging Help

[dmelt253]

Hello,

I have a VERY strange error happening in one of my Macros that for the life of me I sure can't figure out. The weird thing is that this was working just fine before and now the error has crept in somehow.

Basically I was using a very basic formula in H1 that just calls a value from F1, inserts some text and then also calls a value from G1.

So here are the lines of code giving me trouble:

Range("H1").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]&""   M.O. Line#:  ""&RC[-1]"
    Range("H1").Select
    Selection.AutoFill Destination:=Range(Range("H1"), Range("G1").End(xlDown).Offset(0, 1)), Type:=xlFillDefault
    Range(Range("H1"), Range("H1").End(xlDown)).Select

The weird part is the formulas fill in just fine but what gets displayed in each cell is just WRONG as you can see by the screen shot below:

You can't see it in the screen shot but my cursor is in H3 so the formula it is showing is saying it should display
29357388 M.O. Line#: 66

It basically should auto fill and pull the corresponding value from columns F & G for that row but is just copies H1 all the way down regardless of what the formula says.

WTH?

 

[Norie]

Is automatic calculation turned on?

 

[dmelt253]

I am not familiar with that concept but I will use my Google-Fu. Thanks!

 

[Norie]

On the Formulas toolbar goto the Calculation Options dropdown and check Automatic is selected.

You can also calculate the entire workbook by pressing F9 and the active sheet with SHIFT+F9.

 

[dmelt253]

I ended up turning on auto calculation with a line of code right before the part that was giving me trouble. The odd thing about this whole issue is that it was only happening when something was typed into an InputBox at the beginning when the Macro first runs. If this input is left blank then it worked just fine. The message box doesn't have anything to do with this formula either. It just populates the header of my document.

Excel is weird sometimes.

 

[Norie]

Could the code be setting calculation to manual at some point?

That is kind of a common practice to speed up code especially if working with a lot of formulas.