• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet Change Event that inludes VLOOKUP (Value Only)

E

excelexia

New Member
Good evening all,
This is my first post so bear with me.

Quick request summary:
When cell C6 changes, I need cells E14:E29 to process a vlookup and then hold that value so that it does not change.

Why?

I created a recipe calculator at work. The calculator I created has 100 identical calculator sheets. The ingredient sku #s are on column B and the ingredient descriptions are on column C (C14:C29). C6 is where the operator inputs the amount of pounds he/she is requesting. Column E is where the lot #s are supposed to go. I tried to use vlookup formulas on column E so that it pulls the lot number when the ingredients pop up, but I realized it wouldn't work as lot numbers change constantly, and the vlookup formulas on the already processed sheets would change as the lot numbers are updated.

My current thinking is that it can be done through a vlookup within a worksheet change event that then turned the vlookup into a permanent value.

Either that or find a way to put a calculation limit on E14:E29 so that it only calculates once even if the return value changes in the referenced table array in vlookup.

I apologize in advance if I do not make sense. I am trying to learn as much excel as possible- the lingo, the formulas, VBA, etc...

All suggestions are welcome.

Thank you for your time.

EDIT: I uploaded a sample file for you guys to check out. Hope it helps.
 

Attachments

Hi ,

I am somewhat confused by your description ; please clarify.

1. Your workbook has 100 sheets , presumably for 100 different dishes ; is this correct ?

2. Each dish is independent of the others ; correct ?

3. When you select a dish from the list of dishes , the description is entered in cell C5 , and from this the required ingredients are retrieved.

4. Why do want the lookup to be based on a Worksheet_Change event ?

Narayan
 
NARAYANK991 said:
Hi ,

I am somewhat confused by your description ; please clarify.

1. Your workbook has 100 sheets , presumably for 100 different dishes ; is this correct ?

2. Each dish is independent of the others ; correct ?

3. When you select a dish from the list of dishes , the description is entered in cell C5 , and from this the required ingredients are retrieved.

4. Why do want the lookup to be based on a Worksheet_Change event ?

Narayan
Click to expand...



1. They don't have to be different. I just want to track what doughs are being made and how often.

2. Each dough is independent of the others.

3. Correct.

4. Because I am tracking the lot numbers for each ingredient, and the lot numbers change often for each ingredient. Say, for example, there are two available lots for flour. The first mix (Sheet 1) requires all the flour available from lot 1. I then go and update the Lot Number tab so that the ingredient flour has the updated lot number (Lot 2) for the next mixes. What happens is that the first mix will show (because of the vlookup formula) Lot 2 and not Lot 1.

I know I could just copy and value paste the whole sheet after each mix but I am not using the calculator. I need it to be as automated as possible. The worksheet change event would be useful so that (in my mind) the lot numbers would appear and would not change on sheets that have already been used.

Like I said though, I am open to suggestions!
 
Hi ,

OK. So what is the basic trigger on each sheet tab ?

After the quantity in lbs. is entered on any particular sheet , does the user do anything else ?

If not , the Workbook_SheetChange event macro can be used ; the Worksheet_Change macro is particular to a sheet ; if you have 100 sheets , and you want the same behaviour to be replicated on each of them , you would have 100 copies of the same Worksheet_Change macro. The Workbook_SheetChange event macro would be just one ; since it has two parameters specifying the relevant Sheet and the relevant cell , you would be able to get the same functionality from just one copy of the macro.

Narayan
 
The basic trigger would be cell C6, where the user inputs the amount of lbs he/she wants to mix. The Workbook_SheetChange sounds good! The problem for me is coming up with the macro.
 
Hi ,

So what is it that is to be done when the macro is triggered ?

If the macro converts the formulae to values , then the next time C6 is changed on that sheet , the dependent values will not change since the formulae have been overwritten by values.

The only solution is that all the calculation is done within the macro ; if so , this will take some time to write , since your worksheet has 15 columns each with its own formula. Or is column E the only one which is dependent on lot numbers ? If so , only this one column can have the formula within the code.

Narayan
 
That is correct Narayank, only column E (E14:E29) is dependent upon the lot numbers. Recipes rarely change as far as formulations go so I am not worried about anything else being captured, only the lot numbers.
 
Hi ,

Can you try this macro ? Copy and paste it in the ThisWorkbook section of your VBE :

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
            If Application.Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
            If Target.Count > 1 Then Exit Sub
           
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Sh
                Col_E_lastrow = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
           
                .Range("E14:E" & Col_E_lastrow).Value = .Range("E14:E" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
End Sub
Narayan
 
I tried it seems like nothing happened.

Something that just came to me: I have other unique worksheets within the workbook that will probably be affected by this macro, correct? I think it would be better to create a worksheet change event macro no?

BTW thank you for investing your time in solving my excel problem.
 
Hi ,

Can you check the uploaded file ? Enter data in C5 using the drop-down , and then enter a quantity in C6 ; the formulae in E14 through E16 will be overwritten as values.

Narayan
 

Attachments

Actually, I think this might work beautifully.

I didn't see that the values were overwritten.

Can you make it a worksheet change? I sometimes update other worksheets within the workbook and that may trigger the macro.
 
Hi ,

You can go through the macro , and you can see there are two checks :

1. This macro will do something only if C6 is changed.

2. Even if you enter a value in C6 , it checks to see that you have already selected C5 using the drop-down ; when ever you make a selection using the drop-down E5 is calculated using a VLOOKUP. If E5 does not have a value , then even if you put in a value in C6 , nothing will be done.

Also , as I have mentioned earlier , if you make this a Worksheet_Change event macro , you will need to enter it in every sheet ; can you do that ?

Narayan
 
Hi ,

Try this :
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
            If Target.Count > 1 Then Exit Sub
           
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Me
                Col_E_lastrow = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
           
                .Range("E14:E" & Col_E_lastrow).Value = .Range("E14:E" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
End Sub
Narayan
 
Narayan,
Thank you so much. You've been an awesome help.

One last thing: Where can I learn VBA? Is there an online resource that can give me the basics?
 
Another thing: Can we make it so that the lookup fires up everytime a dough from the drop down is selected? In case the user selects the incorrect dough (from the drop down) the first time?
 
Hi ,

Sure ; replace all of the code in any Worksheet section with this :

Code: 
Private Sub ComboBox1_Change()
            Call Copy_Paste_Lots
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
            If Target.Count > 1 Then Exit Sub
           
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Me
                Col_E_lastrow = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
           
                .Range("E14:E" & Col_E_lastrow).Value = .Range("E14:E" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
End Sub
 
 
Public Sub Copy_Paste_Lots()
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Me
                Col_E_lastrow = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
               
                .Range("E29").Copy Destination:=.Range("E14:E28")
                Application.CutCopyMode = False
               
               
                .Range("E14:E" & Col_E_lastrow).Value = .Range("E14:E" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
 
End Sub
Narayan
 
Hey Bob,
Just trying to bump my thread so it is not forgotten.

I don't mean to offend those that are offering their help.
 
Narayan,
Thank you for messaging me back. I fixed the formulas but it still did not work.

Again, thank you for your time invested. I have been reading through the VBA links you provided and they've been very helpful.

I am practicing by recording macros and then looking at the code.

I am a long ways away from getting this down though.
 
All post deserve equal treatment, forum members do not forget posts they do their best, do you not think bumping posts displays bad manners.
 
Ok,
I think I solved it.

I played around with the code and got:

Code: 
Private Sub ComboBox1_Change()
            Call Copy_Paste_Lots
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Range("C6")) Is Nothing Then Exit Sub
            If Target.Count > 1 Then Exit Sub
           
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Me
                Col_E_lastrow = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
           
                .Range("E14:E" & Col_E_lastrow).Value = .Range("E14:E" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
End Sub
 
 
Public Sub Copy_Paste_Lots()
            If Trim([E5]) = vbNullString Then Exit Sub
           
            Application.EnableEvents = False
           
            With Me
                E30 = Evaluate("=LOOKUP(9^9,0+" & .Range("Lot_Numbers").Address & ",ROW(" & .Range("Lot_Numbers").Address & "))")
               
                .Range("E30").Copy Destination:=.Range("E14:E29")
                Application.CutCopyMode = False
               
               
                .Range("E14:E29" & Col_E_lastrow).Value = .Range("E14:E29" & Col_E_lastrow).Value
            End With
           
            Application.EnableEvents = True
 
End Sub
 
You must log in or register to reply here.
Back
Top