How to input data into cells when criteria is met - macro / VBA?

[andyjp2003]

Please can anyone give me any assistance on the following?
I want to set something to input data/values into cells automatically when criteria is met from a particular drop down box.

I assume this can be done by a macro in VBA - i do not have much experience in VBA (sorry)
But other than that I consider myself quite app in other areas in excel

Example:
A1=drop down box from criteria

Criteria info
C1=pelmet
D1=2500
E1=60

C2=Cornice
D2=2500
E2=90

A10=length (2500)
A11=Height (60)

So if criteria is selected it will override any data already inserted in A10 and A11.

I hope this makes sense - thank you guys.
Andy.

[rylo]

Hi

A10: =VLOOKUP(A1,C1:E2,2,FALSE)
A11: =VLOOKUP(A1,C1:E2,3,FALSE)

HTH

rylo

[andyjp2003]

Hi Rylo,

Thank you for you reply, I have thought of this but unfortunatly the cells A10 and A11 are available for user input.
For example, in the drop down box there is another field "Door", therefore the user will have to put in the sizes required in A10 and A11.
However, if from the drop down box they select "Pelmet" I want it to update the cells A10 and A11 with the values shown as they are the fixed values for this part.

Hope this makes sense - msg me if not

[rylo]

Hi

How about a change event

Please Login or Register  to view this content.

rylo

[andyjp2003]

Thank Rylo, I will look into that, vba is all new to me at mo, but can just about understand what the code is gonna do will come back to u later.
Cheers, Andy

[andyjp2003]

Hi Rylo,

This worked a treat
I have configured this into my worksheet that I am working on, however, I have 25 more rows I need to configure this 'Change event' ?
Is there a simpler code or using a range in order to account for all the rows?

Below is the code for row one which hopefully will give you an insight to where the data cells are required.

Please Login or Register  to view this content.

So to clarify, the drop down cells are in column 'F'
the data input cell is D34 + E34 (e.g. 2500 + 60) which goes down to row number 58.

The drop down info range is V3:X23 (where the info of product and sizes are i.e. pelmet, 2500, 60)

However there is an issue, I have in the drop down list, doors, frame, and End panel, when any of these is selected I do not want the values in cell columns D + E to be cleared/changed, I will need them as entered by the user. Is there a way round this?
I hope this makes sense?
Cheers
Andy

[rylo]

Hi

Can you please edit your last post and put the code within code tags.

For the range, you could use the intersect function

Please Login or Register  to view this content.

Then instead of using the specific address, you use the target

Please Login or Register  to view this content.

Same thing for the d34 and e34. You could either use the cells construct or the offset construct

Please Login or Register  to view this content.

Re the exclusions, then you could put in another IF statement to test for the specific items, and only process if it doesn't match, or you could use a select case, do nothing for the match, and process the rest as an else condition.

Hope that makes sense

rylo

[andyjp2003]

Hi Rylo,

Sorry if my reply is me bring a little bit thick ;-) "Editing the post to put the code within code tag?" Can you elaborate?
Sorry I am new to this forum :-(

I will look into your reply which I am very grateful for - thanks again Rylo.
I will come back to you very shortly.
Andy.

[andyjp2003]

Hi Rylo,


I think I got myself confused

This is the code I have put in, I am def. sure I have got it wrong !

Please Login or Register  to view this content.

Do you think u could help me out a little - pretty pls
I have had to move some columns and rows a bit as I had to modify the program a little - sorry.

Drop down box cell = D43:D67
Length (2500) = E43 down to E67
Width (60) = F43 down to F67


PS. managed to sort the tags out on the code.
Cheers
Andy

[rylo]

Hi

More like

Please Login or Register  to view this content.

rylo

[andyjp2003]

Rylo,

Ur a genius, thank you ever so much for helping me out...
I have another idea for my excel program; dealing with check boxes, out of 3 boxes only 1 can be checked, so if an unchecked box in checked then the other is to be unchecked?
Is that possible to do in VBA?

Will I be ok to contact you privately if I have any other issues with VBA?
Cheers buddy
Andy

[rylo]

Andy

If you want to have mutually exclusive selections like that, it would be better to use radio buttons not check boxes. Inbuilt functionality is better to use.

It would be much better to post your issues / questions in the general forum rather than privately as you get a much larger contributing community. I may not always be available to respond, so others can take up the challenge.

rylo