Solved auto populate text from data validation list

Mstest / Awrdacpi
July 14, 2015 at 04:12:24
Specs: Windows XP, 2 GHz / 958 MB
Hi,

I want to auto populate my data in column H. It's a vlookup in macro.
I have a data validation list in Column G from row 12 to 100 and I want to auto populate the description in column H based on what I selected in column G.

I try to run my code it automatically closes my excel. Please help in my code.

my code is here:

Private Sub Worksheet_Change(ByVal Target As Range) 
     If Selection.Row >= 12 Then
        Cells(Target.Row, 8).FormulaR1C1 = _
         "=VLOOKUP(cells(target.row,7),'Pathfinder All Cat Types'!C[-6]:C[-2],2,0)"
         ActiveCell.Offset(1, 0).Range("A1").Select

       End If
End Sub

It's like when I choose Basket 123 the description would be "Plastic" on the same row.

Column G = Basket
Column H = Description

Please help.

Thank you.

message edited by shieldbreakers


See More: auto populate text from data validation list

Report •

✔ Best Answer
July 16, 2015 at 11:30:37
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made in desired range
     If Target.Column = 7 And Target.Row >= 12 Then

'If Yes, Disable Events, Insert Formula(s), Enable Events
        Application.EnableEvents = False
'Loop through changed cells, insert formula at each change
          For Each rngCell In Range(Target.Address)
             Cells(rngCell.Row, 8).FormulaR1C1 = _
               "=VLOOKUP(RC[-1],'Pathfinder All Cat Types'!C[-6]:C[-2],2,0)"
          Next
        Application.EnableEvents = True
           
     End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
July 14, 2015 at 07:41:08
I believe the reason that your code is crashing is (at least) two-fold.

You are using the WorksheetChange event to change the worksheet and the change you are making is causing an error in the spreadsheet.

Allow me to explain:

First, the user makes a change (choosing an item from the drop down) which triggers the Change event. Then the code makes a change (writing the formula to the cell) which once again triggers the Change event. The code then writes the formula again which triggers the Change event and so on and so on. In the best of cases, the Worksheet_Change event fires twice and then stops once the "second" change is made and the user doesn't even notice that the code fired twice. In the worst of cases, the code goes into a loop of change/fire/change/fire which sometimes it recovers from and other times it doesn't. In your case, it appears that the code is going into an infinite loop and eventually crashing.

The way to eliminate the "multiple fireing" issue is to disable events, allow the code to make the change and then enable events again:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Row >= 12 Then
        Application.EnableEvents = False

           'Code to change worksheet goes here
       
        Application.EnableEvents = True
    End If
End Sub

Now, the thing to be aware of when using this technique is that if your code stops after executing the Application.EnableEvents = False instruction but before executing Application.EnableEvents = True, Events will be disabled until you either enable them within VBA for that session or close Excel and re-open it.

Many of us have been "caught" by that situation during code debugging sessions and have spent way too much time trying to figure out why our Event codes weren't working. We think that there is something wrong with the code when in reality it simply is not firing because Events were disabled and never enabled again.

OK, with that situation taken care of, let's look at your actual code. First, I'm not quite sure why you are using:

If Selection.Row >= 12 Then

and not

If Target.Row >= 12 Then

You will note that the declaration in the first line of the code is:

ByVal Target As Range

I don't think I've ever seen Selection.Row used in a WorksheetChange event, but I'm certainly willing to learn why it might be used.

Now, as for the formula that you are trying to put in the cell...

Since you have enclosed entire formula is quotes, VBA is writing the cells(target.row,7) string in the cell as part of the formula. It is not converting cells(target.row,7) to a cell reference. You will note that VBA does not capitalize the cells(target.row,7) because it sees it as a text string. Once you eliminate the crash (by eliminating the multiple firing of the event code), I believe that you will see a #NAME error in the cell where the formula is being written.

I think that you want to use this:

=VLOOKUP(RC[-1],'Pathfinder All Cat Types'!C[-6]:C[-2],2,0)

So, to wrap up the crash issue, I think what is happening is that the code is going into an infinite loop by trying to continually write an invalid formula into the cell and eventually gives up in a very messy manner.

Two other comments about your code, one minor, one major (I think)

Major: Your code is checking the Row where the change is made, but not the Column. This means that it will fire and insert the formula into Column H when a change is made in any Column, not just a change to your Column G drop downs. Unless you have restricted data entry into all other columns, you may end up with Column H formulas when you don't want them. Since I don't know the layout or operation of your worksheet, I don't know if that is an issue or not.

Minor: I don't know why you are using this instruction:

ActiveCell.Offset(1, 0).Range("A1").Select

Is there a reason you are selecting a cell 2 Rows down from where the change was made?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 14, 2015 at 13:26:36
BTW...you don't have to put a VLOOKUP formula in the cell and then have Excel return the value from the other sheet.

You could use Find in VBA and populate Column H with a value, not a formula. In terms of overall workbook efficiency, that might be better since the VLOOKUPs will all recalculate every time the sheet does.

Off course, if the values in your table will change over time and will need to update in Column H when the change, then the VLOOKUP makes sense.

Just tossing an option out there...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
July 14, 2015 at 21:17:44
Hi, DerbyDad03

The file is actually a template wherein the user will input value in Column G and column H should populate the description. I only did a vlookup formula for each cell in Column H but the file is making big in size so I thought of writing it into vba.

I first did a record macro to get the code in writing a vlookup formula and now I am having a hard time in writing the code.

Will it be possible if you could help me with this?
Basically the validation list come from other sheet in the file. (say Sheet2)
Sheet1 is the main sheet where the user will input information.

Whenever I select in column G, column H should have a description.

Code	Description
911	Gap
912	Other
913	Obsolete
914	DO NOT USE

If I select Code 913 in column G in any row after row 12 it will populate the description on the next column same row (H)

Hope it makes sense.

Thank you.


Report •

Related Solutions

#4
July 15, 2015 at 06:40:44
I believe that I provided all of the info you needed to fix your code, you just needed to put it all together. ;-)

That said, here are 2 options to accomplish your goal:

Option 1 - Using VLOOKUP formulas on Sheet1

This code will put a VLOOKUP formula in the Column H each time a change in made to Column G, from Row 12 and beyond. One advantage of using VLOOKUP is that you can make changes to the table on the "Pathfinder All Cat Types" sheet and any VLOOKUP formulas that already exist will update automatically. One possible disadvantage is that all VLOOKUP's will recalculate every time Sheet1 recalculates. This is probably an insignificant issue, I simply mention it to help explain why I am offering a second option below.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made in desired range
     If Target.Column = 7 And Target.Row >= 12 Then

'If Yes, Disable Events, Insert Formula, Enable Events
        Application.EnableEvents = False
          Cells(Target.Row, 8).FormulaR1C1 = _
             "=VLOOKUP(RC[-1],'Pathfinder All Cat Types'!C[-6]:C[-2],2,0)"
        Application.EnableEvents = True
     
     End If
End Sub

Option 2 - Using VBA Find to hardcode the value in Column H

This code will use the VBA Find method to find the Target value on Pathfinder All Cat Types Column B and copy the value from Column C into Sheet1 Column H. The disadvantage to this method is that if you change the values in Pathfinder All Cat Types Column C, the values on Sheet1 will not update automatically. The drop down would need to be accessed again to force the code to run and replace the value in Column H with the new data. If the data in the Pathfinder All Cat Types sheet is static, then this method is slightly more efficient from an Excel perspective since there are no VLOOKUP formulas to recalculate.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made in desired range
     If Target.Column = 7 And Target.Row >= 12 Then

'If Yes, Disable Events, Find Target value in
'Pathfinder All Cat Types Column B

           With Sheets("Pathfinder All Cat Types").Range("B:B")
              Set d = .Find(Target, LookIn:=xlValues)
           End With
           
'Disable Events, Place value from Pathfinder All Cat Types Column C
'Into Sheet1 Column H, Enable Events
        Application.EnableEvents = False
            Cells(Target.Row, 8) = d.Offset(0, 1)
        Application.EnableEvents = True
        
     End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 16, 2015 at 00:09:04
Hi, DerbyDad03

thank you for the help. I tried and it works now.

I just have another question since the code will work if you trigger to select it from the list but would it be possible to automatically update the column H if the user pasted more than 1 values in column G?

Like if the user has 10 values from another file and just pasted it in the template.
I did try and only the first row in the list was updated the remaining rows needs to edit first before it will update.

Thank you.


Report •

#6
July 16, 2015 at 06:27:21
I know that you are new to VBA and therefore also new to posting questions about VBA, so please take the following in the spirit that it is intended.

Your original post, as well as Response #3, specifically state that the user will be choosing values from a Data Validation list. You have now added an additional requirement that the code retrieve values based on the input of multiple pieces of data at one time.

As you have seen from your attempts to fix the recorded code, VBA is very, very "specific" and needs to be told exactly what to do and when to do it. Once a macro is written to meet a specific set of requirements, it is not always easy to simply toss more requirements at it and expect it to work. The two options are either to start from scratch and rewrite the original code or "bolt-on" a new section which often results in bloated, inefficient code. In addition, those of us that are writing the code have to set up test sheets again, test the new code, etc.

We often see situations like this in these forums.

"Hi, I need some code to do this."
"Thanks! That works great. Now can you make it do this?"
"Perfect. Now, one more thing. I would also like it to do this."

By the time we are done, the final version looks nothing like the original code and we have done a bunch of extra work to get to the final solution. I understand the thoughts process of "Let's keep this simple" when asking the original question, but in many cases, a "simple request" ends up being more work once all of the other requirements are eventually disclosed.

I respectively request that in the future you gather all of your requirements and post them as part of the original request so that we all know exactly what goal we are trying to achieve.

In the meantime, I will work on your code and see what needs to be done to meet the new requirements.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
July 16, 2015 at 11:30:37
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made in desired range
     If Target.Column = 7 And Target.Row >= 12 Then

'If Yes, Disable Events, Insert Formula(s), Enable Events
        Application.EnableEvents = False
'Loop through changed cells, insert formula at each change
          For Each rngCell In Range(Target.Address)
             Cells(rngCell.Row, 8).FormulaR1C1 = _
               "=VLOOKUP(RC[-1],'Pathfinder All Cat Types'!C[-6]:C[-2],2,0)"
          Next
        Application.EnableEvents = True
           
     End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
July 19, 2015 at 20:07:00
thank you for the help.

Report •

Ask Question