Code to Calculate Certain Cells Upon User Entry

[Mayanwolfe]

Hi,

I have a workbook with a "landing page" where users enter some information, then click a button to run a macro that calculates the (large) workbook. Only the landing page is set for automatic calculation. This works fine, but at one point on the page the user enters a ZIP code and I want it to return the correct county name. However, my county lookup stuff is on another sheet, so the county doesn't update when the user enters a new ZIP. Basically, I need VBA code that will calculate specific cells on another sheet only when the user enters (or changes) info in a specific cell on the landing page.

ZIP Code entry is in cell I26 on sheet "LandingPage"
County should be returned in I27 on the same sheet

All the relevant calculation shenanigans take place in cells MQ8:MR14 on sheet "RatingTables"

I really appreciate any help you guys can give, previous experience has proven that you're all awesome.

 

[Jeffrey Lopez]

There are two ways of doing this, you can either A: use a Look Up in I27, so something like:

=VLOOKUP(I26,COUNTRYRANGE,2,false)

That would work for you... but if you don't want a formula in the cell you need to do (B a Sub in the Worksheet, right click on the Worksheet name, click View Code and Paste this:

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ErrorHandler:
If Intersect(Target, Range("I26")) Is Nothing Then Exit Sub


If Target.Value > 0 Then
Range(Target.Address).Offset(1, 0).Value = "PUT FORMULA HERE TO CALCULATE THE COUNTRY"
End If


ErrorHandler:
End Sub

 

[Mayanwolfe]

Hi FearNotTheWrath, thanks for the response! That looks like it would most definitely work if all sheets calculated automatically, but unfortunately they don't (it's a very large workbook).

The setup I currently have is this:

1. The user enters the ZIP into I26 on the LandingPage sheet.
2. This ZIP is transferred to the RatingTables sheet and there the state and ZIP are checked against a PivotTable using INDEX/MATCH.
3. The corresponding county is returned to another cell on the RatingTables sheet.
4. The county appears below the ZIP on the LandingPage.

As I said, this works perfectly if everything is set to automatic, but since RatingTables is set to manual, when the user changes the ZIP on the LandingPage, nothing happens. What I'm looking for is code that will detect when the user enters a different ZIP code in I26, and calculate only the cells on RatingTables required to return the county, nothing else. Those cells are MQ8:MR14.

EDIT: Due to certain format restrictions, the county lookup must take place on the RatingTables sheet, not the LandingPage.

Hope that clarifies things a bit. Again, thanks for the help.

 

[Jeffrey Lopez]

This would still apply... in that you would just need this:

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ErrorHandler:
If Intersect(Target, Range("I26")) Is Nothing Then Exit Sub


If Target.Value > 0 Then

' PUT CODE HERE THAT RUNS THE NEXT STEP TO CALCULATE THE COUNTRY CODE.


End If




ErrorHandler:
End Sub

So all this does is say, that if anything is added to I26, then it will run the code... does that make sense?

 

[Mayanwolfe]

Oh, okay, I understand where this is coming from now. I was a little slow in the uptake. I can definitely make this work! Thanks so much, FearNot! You saved my bacon!

 

[Jeffrey Lopez]

Glad to help!