Drop down list question

[ipbr21054]

I enter my mileage onto a spreadsheet for various garages.
I wish to have a drop down list with the garages name & miles to & from there.

In the drop down list i would need to see the name of the garage & also the miles BUT when i make my selection i would only require the mileage to be entered into the cell.


The worksheet is called MILEAGE & the drop down list will be placed into cells D3:D30

Is this possible if so please advise.

Many thanks.

 

[ipbr21054]

Thanks for that.
Ive just popped out for half hour but asking if I type b and not B then the code will not work.
How can it be written so b or B will enter 1
small & upper case.

 

[rpaulson]

If ucase(Target.Value = "B") Then Target.Value = "1"

 

[ipbr21054]

Hi,
I have applied the code you mentioned as show below.
The problem is say i type W in cell D3 and then i move to cell D4, cell D3 still shows W, If i then click back into cell D3 it only then changes to 5

With my code shown above as soon as i moved from the cell in question the letter would change to its numerical value.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Selection.Count = 1 Then
If Not Intersect(Target, Range("D3:D30")) Is Nothing Then




If Target.Value = "b" Then Target.Value = "1"
If UCase(Target.Value = "B") Then Target.Value = "1"
If Target.Value = "l" Then Target.Value = "4"
If UCase(Target.Value = "L") Then Target.Value = "4"
If Target.Value = "w" Then Target.Value = "5"
If UCase(Target.Value = "W") Then Target.Value = "5"
If Target.Value = "h" Then Target.Value = "6"
If UCase(Target.Value = "H") Then Target.Value = "6"
If Target.Value = "c" Then Target.Value = "7"
If UCase(Target.Value = "C") Then Target.Value = "7"


End If
End If


End Sub

 

[rpaulson]

here is the entire code you need

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("D3:D30")) Is Nothing Then


If UCase(Target.Value) = "B" Then Target.Value = "1"
If UCase(Target.Value) = "L" Then Target.Value = "4"
If UCase(Target.Value) = "W" Then Target.Value = "5"
If UCase(Target.Value) = "H" Then Target.Value = "6"
If UCase(Target.Value) = "C" Then Target.Value = "7"


End If

End Sub

hth,

Ross

 

[L. Howard]

Give this a try.
Works for the garage name like Regal Garage 26 where it returns 26, but if you have a garage name like Mick's Rte. 44 grg 66 then it will return 4466.

Copy the code to the MILAGE sheet module. When you select a garage from a drop down in the D3:D30 you get the number in the adjacent cell.

Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Intersect(Target, Me.Range("$D$3:$D$30"))
If isect Is Nothing Then Exit Sub

Dim tADD As String

tADD = Target.Address

With Target.Offset(, 1)
  .Formula = ("=SUMPRODUCT(MID(0&" & tADD & ",LARGE(INDEX(ISNUMBER(--MID(" & tADD & ",ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)")
  .Value = .Value
End With

End Sub

 

[ipbr21054]

This works spot on.

Many thanks for the input givenm

 

[L. Howard]

Give this a try.
Works for the garage name like Regal Garage 26 where it returns 26, but if you have a garage name like Mick's Rte. 44 grg 66 then it will return 4466.

Copy the code to the MILAGE sheet module. When you select a garage from a drop down in the D3:D30 you get the number in the adjacent cell.

Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Intersect(Target, Me.Range("$D$3:$D$30"))
If isect Is Nothing Then Exit Sub

Dim tADD As String

tADD = Target.Address

With Target.Offset(, 1)
  .Formula = ("=SUMPRODUCT(MID(0&" & tADD & ",LARGE(INDEX(ISNUMBER(--MID(" & tADD & ",ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)")
  .Value = .Value
End With

End Sub

Sorry about this post here! Seems to be in the wrong thread to be sure... I have no idea how I got here instead of the thread looking to extract a number from a text string.

Howard

 

[L. Howard]

Sorry about this post here! Seems to be in the wrong thread to be sure... I have no idea how I got here instead of the thread looking to extract a number from a text string.

Howard

I remain confused... my post was indeed intended for this thread. Don't know if OP used it or not. Appears the problem was solved.

Howard