vlookup
I still need help with writing this program...can anyone
help? my range is L2:M12 I am using 10 letters to represent numbers 1,2..8,9,0 those letters are to be typed in the "D" column, no more than 6 consecutive letters (they will not represent a value greater than $9,999.99 Please help... |
vlookup
Your post does not state what you are confused about.
It also doesn't state what problem you want to solve. You can use data validation to restrict entries in column D It is unclear how range L2:M12 plays in the problem. It is unclear what you want the "program" to do. -- Regards, Tom Ogilvy "still confused" wrote in message ... I still need help with writing this program...can anyone help? my range is L2:M12 I am using 10 letters to represent numbers 1,2..8,9,0 those letters are to be typed in the "D" column, no more than 6 consecutive letters (they will not represent a value greater than $9,999.99 Please help... |
vlookup
You can't type letters into a cell in column D and have them appear as
numbers using a formula. You would need to write code for that (or perhaps use autocorrect - but I would imagine that might be complex - setting it up and clearing it using selectionchange event). right click on the sheet tab of the worksheet where you want this behavior select view code. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrHandler Dim sStr As String, sStr1 As String Dim i As Long, sChr As String, res As Variant If Target.Count 1 Then Exit Sub If Target.Column = 4 Then If IsEmpty(Target) Then Exit Sub sStr = Trim(Target.Value2) If Len(sStr) 6 Then Target.ClearContents Exit Sub End If If IsNumeric(sStr) Then Exit Sub sStr1 = "" Set rng = Range("L2:M12") For i = 1 To Len(sStr) sChr = Mid(sStr, i, 1) res = Application.VLookup(sChr, rng, 2, False) If Not IsError(res) Then sStr1 = sStr1 & res Else sStr1 = sStr1 & "-" End If Next If IsNumeric(sStr1) Then Application.EnableEvents = False Target.Value = CDbl(sStr1) / 100 Target.NumberFormat = "$ #,##0.00" End If Else End If ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Your post does not state what you are confused about. It also doesn't state what problem you want to solve. You can use data validation to restrict entries in column D It is unclear how range L2:M12 plays in the problem. It is unclear what you want the "program" to do. -- Regards, Tom Ogilvy "still confused" wrote in message ... I still need help with writing this program...can anyone help? my range is L2:M12 I am using 10 letters to represent numbers 1,2..8,9,0 those letters are to be typed in the "D" column, no more than 6 consecutive letters (they will not represent a value greater than $9,999.99 Please help... |
vlookup
Tom Ogilvy wrote:
You can't type letters into a cell in column D and have them appear as numbers using a formula. Well, it certainly isn't clear what the OP is needing to do, but if a is defined to be 1, b is defined to be 2 and d is defined to be 3 (c is not available as a defined name), then typing =a&b&d into Cell D1 will return 123 to that cell. Alan Beban |
vlookup
she has a conversion table in L2:M12
-- Regards, Tom Ogilvy "Alan Beban" wrote in message ... Tom Ogilvy wrote: You can't type letters into a cell in column D and have them appear as numbers using a formula. Well, it certainly isn't clear what the OP is needing to do, but if a is defined to be 1, b is defined to be 2 and d is defined to be 3 (c is not available as a defined name), then typing =a&b&d into Cell D1 will return 123 to that cell. Alan Beban |
All times are GMT +1. The time now is 08:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com