Automatically Tick Cell If Entered Value Matches

  • I have attached a spreadsheet which, when you enter a matching value in the vehicle column, the cell that matches the vehicle name in both instances (column and row) is ticked. Think I am wanting to use a worksheet_calculate function but cannot figure out how to write the appropriate lookup in VBA. I do not want a formula in the cell. Any help appreciated.

  • Re: Calculate Cell Value Based On Other Cell Automatically


    Kia Ora,


    Check out these instructions for using the worksheet_change event.


    Cheers,
    Averil[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi again,


    I've just had a quick chance to look at your spreadsheet. I'm still mostly a learner when it comes to VBA, but this works as long as you have formatted the other cells the same as you have the green ones (e.g. font or else you will just get an "A" in the column).



    You may want to look at having data validation for where the user enters the vehicle type since the select case seems to be case sensitive (???)


    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Calculate Cell Value Based On Other Cell Automatically


    Thanks Averil. I'm wanting to have it a bit more simplistic in terms of code instead of your suggestion.



    I have tried the code above but am unable to get it to work as there appears to be a conflict with another worksheet function on the same sheet.


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Not Intersect(Target, Range("SpecialisationTicks")) Is Nothing Then


    Any suggestions to get around this. Or if I'm on the wrong track I'd like to know.


    Cheers

  • Re: Automatically Calculate Based On Another Cell


    Hey there,


    Sorry, I haven't really had a chance to think about this. Probably out of my league now anyway :? ... thought I'd try my hand at helping out in the forum I get so much out of - guess that didn't work :oops:


    Hopefully someone else can help you troubleshoot your code?


    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Automatically Calculate Based On Another Cell


    Thanks heaps.


    If anyone else can help, much appreciated.


    Pete[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OK, making a little progress I think ...


    Wanting to add ticks via a macro, not on cell change, so that may change things a bit. The code below doesn't work because it comes up with a "runtime 13 type mismatch" error but I feel it's what I'm wanting to do



    I know this is easily done using the formula

    Quote

    =if(A4=B3,"a","")

    but i don't want a formula in the cell. Have attached spreadsheet with macro called test.


    This one is driving me nuts. Any help greatly appreciated.

  • Re: Automatically Calculate Based On Another Cell


    Just out of curiosity, why don't you want a formula in the cell?

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Automatically Calculate Based On Another Cell


    I don't want a formula in the cell as I am wanting to run an advanced filter over the data and advanced filters don't appear to like formula results.


    Cheers
    Pete

  • Re: Calculate Cell Value Based On Other Cell Automatically


    Perhaps

  • Re: Automatically Calculate Based On Another Cell


    Hi,


    Averlip: Your code looked good to me, BTW. Thanks for trying to pass it forward!


    Re your statement: "..since the select case seems to be case sensitive"


    Try using:


    Code
    Select Case Ucase(Target.Value )
                Case Is = "CAR"


    The Ucase changes the Target.Value to UPPERCASE, then you use uppercase in the Case is statement. ie: if the value is cAr or Car or CAr, the Ucase changes it to CAR and the Case Is compares that to CAR....you win!


    PeteNZ:


    Since it looks like you're giving up on the Change_Event (I could show you how to make it play nice with others) here's an option based on your examples.

  • Re: Automatically Calculate Based On Another Cell


    Thanks for that guys and gals. I'm a bit of a novice when it comes to VBA and get a little impatient when I know it should work but doesn't, therefore go off on a tangent to see if there are other ways of doing the job more efficiently. All help greatly appreciated.


    Dave, advanced filter does like formula results? If a criteria for an advanced filter has formulas (say, lookup formulas), should this not affect an advanced filter. I did try it like this but results came back blank. Whereas, when I took out the formula and manually entered the result the advanced filter worked fine. Do you by any chance know what reasoning would be behind this? Many thanks.

  • Re: Automatically Tick Cell If Entered Value Matches


    Hi PeteNZ,


    If you want to figure it out maybe a thread with an example of it (not working) so we can see what you did wrong. Do you remember what you did? <G>

  • Re: Automatically Tick Cell If Entered Value Matches


    Sorry, I have been in NZ on holiday and didn't have a chance to check my emails...


    Hi rbrhodes,


    Thanks for the heads up on using Target.Value - it is added to my growing list of VBA gems from this forum :thanx:


    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!