Populate Form Macro Changing Formulas on Other Sheets

[fredfarmer]

Hello,

Something weird is happening and I cannot figure out what it is. When I enter another AFR# on my AFRsInput sheet in cell D4 it populates that form by the Populate Form macro. For some reason the formulas on the 'AuthorizedFieldReturn' and the 'PartsRequisition' sheets get a bunch of REF#.

I changed the formulas to correct them but each time I select another AFR# in cell D4 of the AFRsInput sheet it goes back to the REF# on those sheets.

Can someone please help to fix this ?

Thank you so much,

Fred

[6StringJazzer]

You don't say which cells show #REF errors. It is difficult to run your code because my first try it told me "invalid AFR number" and I don't know how to make a valid one.

Anyway, it looks like your errors occur in the Parts Used During Repair area. This is because your code deletes the contents of the table Table 1 on the AFRsInput sheet, triggering the reference error. Use clearing instead of deleting:

Please Login or Register  to view this content.

[fredfarmer]

The AFR# list is in the AFRsDB sheet sorry for not mentioning.

[sintek]

The AuthorizedFieldReturn' and the 'PartsRequisition' sheets have formulas that reference the Parts Table...

When you change [D4] the sheet event fires and deletes that table rendering your formulas invalid

[fredfarmer]

I tried that and fixed the formulas on the PartsRequisition sheet.

Then I entered another AFR# in the AFRsInput sheet cell D4 and the formulas went back to REF# errors.

[fredfarmer]

Sintek,

Do I need to reference the AFRsParts instead? That is where they are stored for each AFR#.

[sintek]

You are limiting yourself to no entries with pre-populated formulas
Populate this part "Parts Used During Repair" at the same time as you update your table...

[fredfarmer]

I am not sure what you mean. I am attempting to generate reports to print out for the Parts Requistion and AFRs.

[sintek]

Something like this....Also I would not use a loop ...I suggest filtering and copying the entire range to the table and other sheets...

Please Login or Register  to view this content.

EDit...Another issue...the below range is limited to 15 rows...What happens when there are more than 15 entries...
Untitled.png

[fredfarmer]

Okay I tried your edit of the code. What happens now is the previous parts number of rows gets blanked out but the table remains with those blank rows. The next AFR parts will start at the end of those blank rows on the AFRsInput sheet. Also it takes quite a while for code to run vs before.

[fredfarmer]

I didn't see your edit. The maximum parts per AFR will be 30. We will never have more than that. Usually less than 10 parts per AFR.

[sintek]

The next AFR parts will start at the end of those blank rows on the AFRsInput sheet

Don't clear the table...keep as it was...delete...

Why are you first populating this Parts Table and then having the other 2 sheets referenced from the Table...
Why not just update the sheets immediately

[fredfarmer]

I agree with immediately. When an AFR# is placed in cell D4 the report sheets should also reference that number immediately. But how?

[fredfarmer]

Perhaps I should use an Index Match on the report sheets to reference the AFRsDB and AFRsParts sheets instead of the AFRsInput sheet.

[sintek]

Play around with this....As an example
Also...Merged cells is a no no...Only causes unnecessary issues

Please Login or Register  to view this content.

[fredfarmer]

I have editing to do to remove the merged cells.

Thank you for the help sintek.

[sintek]

With the code in Post 15...From the filter one can easily populate both your required sheets... which does away with the formula references and the Table...
Just need to add rng3 and copy to the other sheet as well...Will have a look later when I return to see if you managed to implement...

[sintek]

Sort out your merged cells...Redesign your forms to make coding simpler...
Play around with this...

Please Login or Register  to view this content.

I suggest look into Userforms and try and steer clear from sheet formulas and sheet event manipulation...