Formula Autofill Debugging Help

dmelt253

New Member
Joined
Jan 12, 2016
Messages
8
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:

Code:
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:
q8tz9Vz.jpg


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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Is automatic calculation turned on?
 

dmelt253

New Member
Joined
Jan 12, 2016
Messages
8
I am not familiar with that concept but I will use my Google-Fu. Thanks!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 12, 2016
Messages
8
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top