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'
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Define General Variables
Dim SIZE
Dim CLASS
Dim MATERIAL
Dim PIPETYPE
Dim GROUP
Dim SCH
Dim PRES_D
Dim TEMP_DU
Dim TEMP_DL
Dim MATFAM
Dim GROUP_PT
'Define Pressure/Temperature Calculation Variables
Dim TEMP_PTL
Dim TEMP_PTU
Dim PRES_PTL
Dim PRES_PTU
Dim PRES_PTDIFF
Dim TEMP_PTDIFF
Dim RATIO_PT
Dim TEMP_INC 'Temperature Increase above TEMP_PTL
'Define Coordinate Calculation Variables
Dim ROW_S
Dim COL_S1
Dim COL_S2
Dim ROW_PT
Dim COL_PT1
Dim COL_PT2
' Set-up Known Variables
CLASS = Worksheets("ASME B31.3 CALCULATION").Cells(4, "D").Value
GROUP = Worksheets("ASME B31.3 CALCULATION").Cells(14, "D").Value
GROUP_PT = GROUP & " - " & CLASS
With Application.WorksheetFunction
ROW_PT = .Match(GROUP_PT, Worksheets("ASME B16.5 TABLES").Range("C3:C142"), 0) 'Determine Row Number for Group
COL_PT1 = .Match(TEMP_DU, Worksheets("ASME B16.5 TABLES").Range("D2:AF2"), 1) ' Determine Column Number for Lower Temperature
COL_PT2 = COL_PT1 + 1 ' Determine Column Number for Upper Temperature
TEMP_PTL = .Index(Worksheets("ASME B16.5 TABLES").Range("D2:AF2"), 1, COL_PT1, 1) ' Determine Lower Temperature Value
TEMP_PTU = .Index(Worksheets("ASME B16.5 TABLES").Range("D2:AF2"), 1, COL_PT1 + 1, 1) ' Determine Lower Temperature Value
PRES_PTU = .Index(Worksheets("ASME B16.5 TABLES").Range("D3:AF142"), ROW_PT, COL_PT1) 'Determine Lower Pressure Value
PRES_PTL = .Index(Worksheets("ASME B16.5 TABLES").Range("D3:AF142"), ROW_PT, COL_PT2) ' Determine Upper Pressure Value
End With
PRES_PTDIFF = PRES_PTU - PRES_PTL 'Determine Pressure Differential
TEMP_PTDIFF = TEMP_PTU - TEMP_PTL 'Determine Temperature Differential
RATIO_PT = PRES_PTDIFF / TEMP_PTDIFF 'Increase in Pressure Per Deg C
TEMP_INC = TEMP_DU - TEMP_PTL
Sheets("ASME B31.3 CALCULATION").Range("M16") = (PRES_PTU - (RATIO_PT * TEMP_INC))
End Sub
Any help/pointer would be very much appreciated :-)
Thanks
Steve
Bookmarks