Method `Range of object_'Worksheet' failed - Trying to write variable to cell

[stevemcd1]

Hi All - Hoping someone can assist.

I am trying to update a spreadsheet I have used for carrying out a wall thickness calculation on piping, and thought it would be a great idea to try and learn VBA at the same time. I have managed to put together some code that generally does what I need (basically finding values in a table of data and interpolating to get intermediate figures) but I am getting an error trying to write the result to a cell.

Error:
Run-time error '-2147417848 (80010108)':
Method `Range of object_'Worksheet' failed

I then get `Not Enough System Resources to Display Completely'

Please Login or Register  to view this content.

Any help/pointer would be very much appreciated :-)

Thanks

Steve

[stnkynts]

You got way too many unsued variable and you didn't actually define any of them. They are all set to variant as default. The big issue is that you are running this as a worksheet change event rather than a macro.

Edit: Holding off on answer till you comply with mod's request.

[stevemcd1]

Apologies, Title updated :-)

stnkynts, there are other calculations that I need to add once I fully understand this first one - the variables relate to future calculations so probably should have removed them for this exercise. :-) Regarding defining them, I am reading up on that now.

I thought the event change would be the best was as I want it to update the calculations as I change the parameters via drop downs and manual entries (pressures, temperatures, materials etc).

Thanks

Steve

[stnkynts]

One of the problems that you are going to run into is a constant loop of worksheet changes. Example: You enter a value in A1 which trigger your macro which put a value in M16. By putting the value in M16 it trigger the macro to occur again, and so on and so on. This can be disabled by setting Application.EnableEvents = False. The best way would be to code the worksheet change event to be range specific so that it only occurs on the change of certain drop downs and manual entries. That information was not provided.

[stevemcd1]

stnkynts, appreciate the responses :-)

I have attached a copy of the file as it currently stands for additional information.

Many thanks

Steve

00 - NEW WALL CALC - VBA.xlsm