Continuing on from my earlier questions, please could i have some help finally integrating the 2 macros and getting the resulting value inserted into the correct cell - the 2 sets of code are below:
This 1st macro is installed into worksheet 1 and is caused to run by a button
Sub test()
first_row = 2
last_row = Range("A" & Rows.Count).End(xlUp).Row
For cur_row = first_row To last_row
a_len = Len(Cells(cur_row, 1).Value)
b_len = Len(Cells(cur_row, 2).Value)
c_len = Len(Cells(cur_row, 3).Value)
e_len = Len(Cells(cur_row, 5).Value)
If a_len > 0 And b_len > 0 And c_len > 0 And e_len > 0 Then
'code to run goes here
Application.Run "'Pricing Calcs V4.xlsm'!price_calc"
End If
Next cur_row
End Sub
This 2nd macro is installed in a module and is run by the 1st macro
Sub price_calc()
On Error GoTo EndMacro
Dim Wb1 As Workbook
Application.ScreenUpdating = False
blind_type = Cells(Selection.Row, 1).Value
fab_width = Cells(Selection.Row, 3).Value
fab_height = Cells(Selection.Row, 5).Value
If blind_type = "Chain-Op Roller in Thames or Dart" Then
ws_name = "R20 Thames-Dart"
ElseIf blind_type = "Chain-Op Roller in Medway or Roe" Then
ws_name = "R20 Medway-Roe"
ElseIf blind_type = "Crank-Op Roller in Thames or Dart" Then
ws_name = "R20C Thames-Dart"
ElseIf blind_type = "Crank-Op Roller in Medway or Roe" Then
ws_name = "R20C Medway-Roe"
ElseIf blind_type = "127mm Vertical in in Thames or Dart" Then
ws_name = "VL30 127mm Thames-Dart"
ElseIf blind_type = "89mm Vertical in in Thames or Dart" Then
ws_name = "VL30 89mm Thames-Dart"
End If
Set Wb1 = Workbooks.Open("N:\11. General\Price Charts.xlsx")
row_location = Application.WorksheetFunction.Match(fab_height, Wb1.Sheets(ws_name).Range("A1:A28")) + 1
col_location = Application.WorksheetFunction.Match(fab_width, Wb1.Sheets(ws_name).Range("A1:S1")) + 1
unit_price = Sheets(ws_name).Cells(row_location, col_location).Value
EndMacro:
Wb1.Close SaveChanges:=False
Selection.Value = unit_price
Application.ScreenUpdating = True
ActiveCell.Offset(1).Select
End Sub
The only problem i now have is that the value selected from the pricing charts needs to be inserted into the column F cell of the relevant row, even if that cell is not selected.
I'm sure that this must be a very simple alteration to my code
Many thanks once again.