Integration of 2 or more macros

0

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.

Answer
Discuss

Answers

1
Selected Answer

MRVMV's solution could work with selecting the cells and you could set the macro to select the correct one by default, but I think it would be easier to just pass the cur_row variable to the new macro.

To do this edit this line of the first macro:

Application.Run "'Pricing Calcs V4.xlsm'!price_calc"

Change it to this:

Application.Run "'Pricing Calcs V4.xlsm'!price_calc", cur_row

Then, change this line in the second macro:

Sub price_calc()

to this:

Sub price_calc(cur_row)

Then change this line in the second macro:

Selection.Value = unit_price

to this:

Cells(cur_row, 6).Value = unit_price

Update

Also change all instances of this:

Selection.Row

to this:

cur_row

This should appear after blind_type, fab_width, and fab_height.

Then, remove this line:

ActiveCell.Offset(1).Select

That should do it.

Here is the link to the microsoft page that explains passing a variable to another macro in a bit more detail. It can be a confusing tutorial though so I will make one here on teachexcel later this week.

Final Macros:

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", cur_row

    End If

Next cur_row

End Sub
Sub price_calc(cur_row)

On Error GoTo EndMacro
Dim Wb1 As Workbook
Application.ScreenUpdating = False

blind_type = Cells(cur_row, 1).Value
fab_width = Cells(cur_row, 3).Value
fab_height = Cells(cur_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

Cells(cur_row, 6).Value = unit_price

Application.ScreenUpdating = True


End Sub
Discuss
0

That's right it's a simple edit in this line in 2nd macro

Selection.Value = unit_price

You should change it to

Cells(Selection.Row,6).Value = unit_price

So the code will be like that (BOLD is the edit)

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
Cells(Selection.Row, 6).Value = unit_price
Application.ScreenUpdating = True
ActiveCell.Offset(1).Select
End sub

NOTE: Selection mean the selected cell and we use Cells to reference specific cell by its row and column number so Selection.Row give us the row of the selected cell and 6 number indicate the column F (by its number).

Discuss

Discussion

Thanks MVRMV but that doesn't work.
It still runs fine and inserts the values just perfect as long as the 1st cell of column F is selected before you start. If any other cell is selected, the macro runs but nothing is inserted.
JonP (rep: 25) Jul 19, '16 at 7:00 am
Did this code work if you run it with 1St cell in Column F selected?
MRVMV (rep: 52) Jul 19, '16 at 7:29 am
yes it ran perfectly if with the 1st cell in Column F was selected.
What i need it to do is run and insert the values into Column F, regardless of the cell that is selected.
JonP (rep: 25) Jul 19, '16 at 7:39 am
I'm a little busy right now, but I will try to correct that after a few hours if no one answer you (especially Don :)
MRVMV (rep: 52) Jul 19, '16 at 8:30 am
I want to ask you something,
Do the 2 macros are in the same file (workbook)?
MRVMV (rep: 52) Jul 19, '16 at 9:56 am
Yes, both macros in same workbook - 1st macro is in sheet 1 and 2nd macro is a module in the workbook
JonP (rep: 25) Jul 19, '16 at 10:11 am
MRVMV, your solution is pretty much spot-on; below, I went another route and included a version that just passes the cur_row variable to the second macro.
don (rep: 1382) Jul 19, '16 at 10:59 am
Sorry Don but it still doesn't work and is no better than MRVMV's answer :(
It works fine as long as the 1st cell in column F is selected before running the macro but if any other cell is selected then the selection just hops down the number of rows corresponding to those that are expecting a value, but doesn't bring any value across (does that make sense?)
JonP (rep: 25) Jul 19, '16 at 11:52 am
I have attached the files above so that you gents can try to see whats wrong.
Fairly obviously, 'Price Charts V4' is the workbook I'm working on and where the macros are - you'll have to change the line...
Set Wb1 = Workbooks.Open("N:\11. General\Price Charts.xlsx") 

... to where you save the 'Price Charts' file.
(feels like I'mprobably trying to teach my grandmother to suck eggs!)
JonP (rep: 25) Jul 19, '16 at 11:59 am
Answer updated. I had just forgotten to replace all of the Selection elements.
don (rep: 1382) Jul 19, '16 at 2:12 pm
Thanks Don that works perfect.
However, I've just thought that actually we don't need 2 seperate macros now do we? - the reason for the 1st macro being seperate and being in the worksheet rather than a module was because it was to be automatic!
I've put both on the module, one after the other, and they work fine but probably even better to actually integrate into 1 single macro?
JonP (rep: 25) Jul 20, '16 at 1:15 am
Honestly, it is fine to keep them separate since you know how they work. Some workbooks have many separate macros and they integrate them by calling each one from other ones as needed. This will make it easier to maintain the macros in the long-run. If you put them together in one macro, it will make it more confusing when you need to change something.
don (rep: 1382) Jul 20, '16 at 12:38 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login