Autopopulate drop down if contains only one value

[MRPUG]

Hi,

I'm new to vba and will be starting to use vba alot more now.

I'm trying to create dropdown's and have it autopopulate if there is only one value. The Validation's is pulling from Tables.
For example column H is pulling base off of column G. But I want column H to autopopulate if it only has one value base off of column G.

I tried to use this code below but I keep getting an error message Run-Time Error '1004' Method range of object_'worksheet' failed:

Please Login or Register  to view this content.

Any help is appreciated,

Thanks!

[mehmetcik]

You are probably creating an endless loop.

Try

Please Login or Register  to view this content.

[MRPUG]

Hi,

Thanks for the reply!
For some reason I still get an error message of the range, am I doing something wrong?? I might be incorporating it with my existing macro wrong.
I have attached the excel workpaper for your reference without your macro for now, any guidance is appreciated.

Thanks!

[mehmetcik]

Ok

I was right you are creating an endless loop but you basic program is wrong too.

I got this to work

Please Login or Register  to view this content.

However it is a pointless bit of code because

Please Login or Register  to view this content.

Simply fills column H with the contents of G1.

It simplifies down to

Please Login or Register  to view this content.

[mehmetcik]

With the code in the Workbook

Try this version

Please Login or Register  to view this content.

[MRPUG]

Hi,

Thanks for the reply! I tried putting the code in, but it seem to clear the whole column H instead of pulling the value automatically if it has only one value.

Am I doing something wrong?

Thanks again for the help.

[mehmetcik]

Which code are you looking at?

Neither attempt to operate as you describe.

Please Login or Register  to view this content.

[MRPUG]

Thanks for the quick reply,

Ah I see, I thought the code I posted before will help with that solution I guess not. Sorry, Would you be able to guide me in the right direction?

Thanks again.

[mehmetcik]

Ok.

Please Specify exactly which cell you change and what you expect to happen.

Meanwhile I will try and come up with something.

Ok lets look at H7

When you click on that cell you are presented with a list that is created using this formula

IF($F7= "Corporate",(OFFSET(INDIRECT("Corp[#Headers]"),MATCH($G7,INDIRECT("Corp[Juris]"),0),2,COUNTIF(INDIRECT("Corp[Juris]"),$G7),1)),(OFFSET(INDIRECT("PTP[#Headers]"),MATCH($G7,INDIRECT("PTP[Juris]"),0),2,COUNTIF(INDIRECT("PTP[Juris]"),$G7),1)))

If you enter that same formula in K7 it will either return an entry or an error.

If an error then proceed with the data validation

Otherwise use the entry.


I cannot paste the code.

Look at the attachment

[MRPUG]

Hi,

This is literally close to what I needed! So basically I want to choose from drop down column F, then choose from drop down column G, but once I choose column G, if the validation value for column H only as one value, it will automatically populate the cell in column H.

I just have a couple questions:
- there seem to be a error message if I click elsewhere on the excel, should I include the "On Error Resume Next"?
- it seem as though in order for column H to populate I will need to click on the cell for it to populate, is there a way to make it populate itself?

Thanks again for the help, I appreciate it!

[mehmetcik]

1. I stripped the macro down to its basics. You can introduce code for error checking.

eg

Please Login or Register  to view this content.

You can always move the code into a normal macro.

Insert a modified formula in all of column K so it returns an empty cell instead of an error.

Then copy Paste values.

You might need to reenter the Data Validation at the end of the Macro.

[MRPUG]

Hi,

Thanks! It seem like just putting the code below helped with the error message:

Please Login or Register  to view this content.

Sorry I'm still new to this, so when you say a normal macro do you mean a module? If I insert the whole code into the module will that solve the issue of being able to have column H automatically update without clicking it?

Thanks again!!!

[MRPUG]

Sorry I have another question, when I highlight a couple cells on column H, lets say H7:H10, it will automatically change the value to H7, was wondering why is that and is that the same reason why Column H only updates if its physically clicked on?

Is there anyway to fix this situation?

Thanks alot for your help again.