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.
Automatically Tick Cell If Entered Value Matches
-
-
-
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).
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then Select Case Target.Value Case Is = "Car" Target.Offset(0, 1).Value = "a" Case Is = "Truck" Target.Offset(0, 2).Value = "a" Case Is = "Motorbike" Target.Offset(0, 3).Value = "a" Case Is = "Hovercraft" Target.Offset(0, 4).Value = "a" Case Is = "Plane" Target.Offset(0, 5).Value = "a" Case Is = "Ship" Target.Offset(0, 6).Value = "a" Case Is = "Moped" Target.Offset(0, 7).Value = "a" Case Is = "Bicycle" Target.Offset(0, 8).Value = "a" Case Else MsgBox ("Oops, that doesn't seem to be an option") End Select End If End Sub
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 -
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.
Code
Display MorePrivate Sub Worksheet_Calculate(ByVal Target As Range) If Not Intersect(Target, Range("TypeSelectionLookup")) Is Nothing Then Dim i As Range Dim j As Range Set i = Range("VehicleHeadings") Set j = Range("VehicleSelectionData") If cell.Value = i Then If cell.Value = j Then cell.Value = "a" Else cell.Value = "" End If End If End If End Sub
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.
CodePrivate 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 -
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
Code
Display MoreDim rtickCell As Range, rtickRng As Range Dim ihead As Range Dim jrow As Range Set rtickRng = Range("TickRange") Set ihead = Range("Headings") Set jrow = Range("Data") For Each rtickCell In rtickRng.Cells If jrow = ihead Then rtickCell.Value = "a" Else rtickCell.Value = "" End If Next rtickCell
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?
-
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
Code
Display MorePrivate Sub Worksheet_Calculate(ByVal Target As Range) If Not Intersect(Target, Range("TypeSelectionLookup")) Is Nothing Then Dim i As Range Dim j As Range Set i = Range("VehicleHeadings") Set j = Range("VehicleSelectionData") Application.EnableEvents = False If Target.Value = i Then Target.Offset(,1).Value = "" ElseIf Target.Value = j Then Target.Offset(,1).Value = "a" End If Application.EnableEvents = True End Sub
-
Re: Calculate Cell Value Based On Other Cell Automatically
Quoteadvanced filters don't appear to like formula results.
False.
-
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:
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 Calculate Based On Another Cell
I can only guess you did something wrong.
-
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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!