Patrick,
Thanx again.
But ... it's not working.
Tried it with the truck example, copied the code properly into the worksheet
"module", but when I drop-down it's just the vlookup return.
Does it work properly for you??
- Mike
"Patrick Molloy" wrote:
> I made three changes
> (1) amending the IF just to check that the cell in A isn't empty,(2) used
> InstrRev to get the last ocurrance of '-' in case rthere are more than one
> instance 'bigbus - a - 1000' for example; and (3) check to see if the
> resultant text is numeric
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count = 1 And Target.Column = 1 And Target.Value <> "" Then
> Dim cell As Range
> Dim text As String
>
> text = Mid(Target.Value, InStrRev(Target.Value, "-") + 1)
> If IsNumeric(text) Then
>
> Set cell = Target.Offset(, 1)
> Do Until Cells(1, cell.Column) = ""
> With cell.Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
> Operator:= _
> xlBetween, Formula1:=text
> End With
> cell.Value = ""
> Set cell = cell.Offset(, 1)
> Loop
>
> End If
> End If
> End Sub
>
>
> "MikeF" wrote:
>
> > Patrick,
> >
> > Thank you for the reply.
> > Uncertain why, but it doesn't work on my sheet.
> > Looks like it should, but I just get the full text from Column A.
> > Also, perhaps I should have specified that "Truck..." is an analogy to each
> > truck's load, which is all over the map, so "Like Truck*" isn't applicable.
> >
> > - Mike
> >
> > "Patrick Molloy" wrote:
> >
> > > the only way I got something to work was a custom data validation, but that
> > > required the user to enter the correct amount - the validation threows an
> > > error if the entry is incorrect.
> > >
> > > That isn't the excercide. what yuo want is to have a blank cell, or have the
> > > drop-down show the correct amount.
> > > So i chose to use the CHANGE event for the sheet. The code checks that the
> > > cell is in column A and that it starts withthe word Truck. Then it updates
> > > the validation rule for each cell in that row with the truck value, and
> > > clears the cell.
> > > Right click the sheet tab, select View Code and paste this code
> > >
> > > Option Explicit
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Count = 1 And Target.column=1 And Target.Value Like "Truck*" Then
> > > Dim cell As Range
> > > Dim text As String
> > > text = Mid(Target.Value, InStr(Target.Value, "-") + 1)
> > > Set cell = Target.Offset(, 1)
> > > Do Until Cells(1, cell.Column) = ""
> > > With cell.Validation
> > > .Delete
> > > .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
> > > Operator:= _
> > > xlBetween, Formula1:=text
> > > '.Formula1 = "0," & text
> > > End With
> > > cell.Value = ""
> > > Set cell = cell.Offset(, 1)
> > > Loop
> > > End If
> > > End Sub
> > >
> > >
> > >
> > > "MikeF" wrote:
> > >
> > > > Uncertain if this can be done as described, but giving it a shot here:
> > > >
> > > > Seattle Portland San Francisco Los Angeles
> > > > Truck 1 - 1,000 $1,000
> > > > Truck 2 - 2,000
> > > > Truck 3 - 3,000 $3,000
> > > > Truck 4 - 4,000
> > > > Truck 5 - 5,000 $5,000
> > > >
> > > > City row text above is in b1:e1 .
> > > > Column text is in a2:a6 , fed by field "TruckPrice" from range "Table" on
> > > > anther sheet, which separates each truck and its price --- fields are Truck
> > > > / Price / TruckPrice [=Truck&" "&Price].
> > > >
> > > > Data validation for cells c2:e6 is the column text, ie the trucks and their
> > > > prices.
> > > > There is a table elsewhere that
> > > > The objective is simple. Examples as outlined above:
> > > > - drop "Truck 1 - 1,000" into b2 and the value in the cell becomes $1,000.
> > > > - drop "Truck 3 - 3,000" into d4 and the value in the cell becomes $3,000.
> > > > - drop "Truck 5 - 5,000" into c6 and the value in the cell becomes $5,000.
> > > > ... And so on for the relevant range.
> > > >
> > > > The solution is apparently not so simple, it dropping down a text field in a
> > > > cell to return a number in the same cell.
> > > > Have tried numberous combinations of vlookup/offset, etc but to no avail.
> > > >
> > > > Can this be accomplished?
> > > >
> > > > Thanx in advance.
> > > >
> > > > - Mike
> > > >
> > > >
> > > >
> > > >
> > > >
Similar Threads
