Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run a macro when a range of cells are filled

1

How do I make a macro run on a particular cell once a range of cells all contain data?

In my worksheet, i need a macro to run in the cell in column F once the cells in columns A, B, C & E of the same row contain data and the last of these has been selected away from: eg, the macro needs to run in F3 once all data entries into A3, B3, C3 & E3 are complete but not before - if A3, B3 & C3 all contain data and the user starts entering a 6-digit number into E3, I don't want the macro to run until they are finished entering the number.

Hopefully the above makes sense but if not please ask.

I know that this should be some sort of event handler but am too green on vba at the moment (learning fast though thanks to this website)

UPDATE

The code I have at the moment is this, but I think I may have replaced too much with the Application.Run line.

Private Sub Worksheet_Change(ByVal Target As Range)
cur_row = Target.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 And Target.Column <> 6 Then
    
    Application.Run "'Pricing Calcs V4.xlsm'!price_calc"
    
End If

End Sub

The main price-calc code that is caused to run is below and the other problem we are getting is when a whole lot of data is copied and pasted in, the macro goes into overdrive and crashes Excel.

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

Please help if there's a simpler way of doing this

UPDATE 2

Perhaps I should say that, as long as the cells are filled one at a time from the left by tabbing on to the next each time, as soon as you tab past the cell in column E and into column F, the macro runs fine.

The problem is only when clicking return instead of tab from the 'E' cell so that it's not the 'F' cell that's selected. 

And then the crash happens if you paste a whole lot of data in at once - it's all like the macro is trying to decide whether or not the cells are all filled. 

Instead of having the macro run automatically, could it be made to run all the way down column F and stop as soon as the row is not filled. This could be activated by a button. 

Any suggestions gratefully received. 

Answer
Discuss

Discussion

Thanks Don
The code works fine except that the macro needs to run in the corresponding cell in column F once all other relevent cells are filled, even if that cell is not selected.
What do I need to change please.
JonP (rep: 37) Jul 13, '16 at 3:24 pm
I'm pretty sure it does that already. When I run it, once the columns a,b,c, and e have something in them for a single row, the value "something" is input into column f in that same row.
don (rep: 1989) Jul 13, '16 at 9:16 pm
If you only want it to run for row 3, then change the cur_row line to this:
cur_row = 3
don (rep: 1989) Jul 13, '16 at 9:18 pm
And then the 'price_calc' macro that runs is below:
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

JonP (rep: 37) Jul 14, '16 at 1:48 am
Sorry I accientally removed the first macro when trying to clean out the blank code section. Can you repost it? But post it in the question as an update b/c it's hard to see in this little section.
don (rep: 1989) Jul 14, '16 at 10:47 am
This is the line that puts a value into the correct cell:
Cells(cur_row, 6).Value = "something"

I can't find that in the macros that you posted.
I think you need to remove:
Selection.Value = unit_price

And put this line in there:
Cells(cur_row, 6).Value = unit_price

But, for that to work, you need to pass the cur_row variable to the other macro. The first example on this page shows you how to do this.
First though, get the macro that I sent you working. Then, get the variable to pass to the pricing macro working. Then get both of them working together.
don (rep: 1989) Jul 14, '16 at 1:16 pm
You are really doing quite a lot in one go. I suggest you get the macro I originally made working and happy in your code. Then lets go for another question on how to integrate both macros. If my last comment solved everything for you then no need to do that.

Also, make sure you are putting a lot of comments in your macros so you will know how to edit this later if you need to.
don (rep: 1989) Jul 14, '16 at 1:19 pm
Sorry Don but that link completely lost me - I'm too new to vba!
The 1st set of code in my update above is what runs automatically and is a direct copy of your code in the answer below, except for my having removed the lines
    'code to run goes here
    Cells(cur_row, 6).Value = "something"
    MsgBox "ready"

and added the following line instead to make the 2nd macro run.
    Application.Run "'Pricing Calcs V4.xlsm'!price_calc" 

Should the following line therefore go back into the 1st macro somewhere, to tell it where to put the answer?
Cells(cur_row, 6).Value = unit_price
JonP (rep: 37) Jul 15, '16 at 1:35 am
Ok, get this macro working by itself to do what you asked in this question. That is the first step. So copy the original one from below and make sure it works outputting the sample data that is currently in it. Don't edit it yet to work with the other macro.

Once that works, then we can move on to integrating the two.

The integration isn't difficult but needs to be done step-by-step.
don (rep: 1989) Jul 15, '16 at 12:49 pm
That worked fine and that's why i tried to alter it.
I've just tried it again and it works just fine as long as you only fill one row at a time; if several rows are completed at once by copy & paste, it only runs on the 1st row.
JonP (rep: 37) Jul 17, '16 at 7:38 am
Correct, that is how it is supposed to work. It can only work on one at a time. Based on your requirements, that was the only way it could run.
don (rep: 1989) Jul 17, '16 at 11:16 am
Okay, instead of running automatically, could it be made to run all the way down column F and stop as soon as the row is not filled? This could be activated by a button. 
JonP (rep: 37) Jul 17, '16 at 11:50 am
Yes but that would be a different macro. I can include that one later today in my answer if you like. I will make it the same basic template like it is now and then you can edit it to suit your needs from there.
don (rep: 1989) Jul 17, '16 at 12:12 pm
Thanks Don, that would be great. 
JonP (rep: 37) Jul 17, '16 at 1:03 pm
Answer updated.
don (rep: 1989) Jul 17, '16 at 3:39 pm
That works just great so please what do i need to change to integrate this with my price_calc macro above?
I tried inserting...
        Application.Run "'Pricing Calcs V4.xlsm'!price_calc"

...instead of...
        Cells(cur_row, 6).Value = "something"

... but it didn't work.
I guess i still need the 'Cells(cur_row, 6).Value' bit somewhere
JonP (rep: 37) Jul 18, '16 at 1:51 am
This is getting a bit hard to piece everything together. If this works, let's consider that a win and move to the next part of the problem, which is integrating this with a macro from another workbook. Open a second question and include two workbooks, each with the macro that they should have and some sample data. Then it will be easier to figure out how to link them best.
don (rep: 1989) Jul 18, '16 at 10:25 am
Don, see update 3 to my question above
JonP (rep: 37) Jul 18, '16 at 10:58 am
This goes back to the link I posted a while back to the microsoft page and is a completely different topic. I would ask that you put that into a separate question so that anyone who comes to this page looking for an answer to your original question will easily find it instead of being confused. The main thing here is that I want to be able to help individual users and also people who come here in the future looking for the same answer.
don (rep: 1989) Jul 18, '16 at 11:18 am
So just remove update 3 and put that into a new question and I will help you work through the implementation that is on this page: microsoft link
don (rep: 1989) Jul 18, '16 at 11:20 am
Add to Discussion

Answers

1
Selected Answer

You want the worksheet change event That link points to the tutorial that explains the concept and provides an example.

In your case, this is probably the code you want:

Private Sub Worksheet_Change(ByVal Target As Range)

cur_row = Target.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 And Target.Column <> 6 Then
    
    'code to run goes here
    Cells(cur_row, 6).Value = "something"
    MsgBox "ready"

End If


End Sub

Update

To have the macro run manually and fill in all of the rows at once, use this version of it:

Sub test()

first_row = 1
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
        Cells(cur_row, 6).Value = "something"

    End If

Next cur_row

End Sub

first_row = 1 is the row where your data starts.

This macro assumes that there is no other data below the data that you want to run the macro on.

Discuss

Discussion

It works! - now see my next stage in a new question...
JonP (rep: 37) Jul 19, '16 at 1:54 am
Add to Discussion


Answer the Question

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