Numbers Not Updating

[johnch121]

A Friend of mine has been helping to develop a small excel file to control our stock of letters an numbers. Everything works with taking away the letters but it does not take away the numbers, also for some reason we have to enter the letters in uppercase for it to work. I would prefer it to be able to work in upper and lower case.

Any help really would be gratefully received.

This is also the code that has been used so far:

Sub Macro1()

1. Dim RegNo As String, RegNoClean As String, RegNoLen As Integer
2. ActiveSheet.Shapes("Rounded Rectangle 1").Select
3. RegNo = Selection.Characters.Text
4. RegNoClean = Replace(RegNo, " ", "")
5. RegNoLen = Len(RegNoClean)
6. Dim RegNoChar
7. For i = 0 To RegNoLen
8. RegNoChar = Left(RegNoClean, 1)
9. Range("A2").Select
10. rowNum = ActiveCell.Row
11. colNum = ActiveCell.Column
12. Set currCell = ActiveSheet.Cells(rowNum, colNum)
13. Do While rowNum < 36
14. If currCell.Value = RegNoChar Then
15. currCell.Offset(0, 1).Value = currCell.Offset(0, 1).Value - 2
16. Else
17. End If
18. rowNum = rowNum + 1
19. Set currCell = ActiveSheet.Cells(rowNum, colNum)
20. Loop
21. RegNoClean = Mid(RegNoClean, 2)
22. Next
23. ActiveSheet.Shapes("Rounded Rectangle 1").Select
24. Selection.Characters.Text = ""
25. With Selection.Font
26. .Name = "Calibri"
27. .FontStyle = "Regular"
28. .Size = 72
29. .Strikethrough = False
30. .Superscript = False
31. .Subscript = False
32. .OutlineFont = False
33. .Shadow = False
34. .Underline = xlUnderlineStyleNone
35. .ColorIndex = 1
36. End With
37. Range("I7").Select

End Sub

[cytop]

Quickie to start...

I would prefer it to be able to work in upper and lower case

VBA is case sensitive - 'a' <> 'A'

You can get around this by converting all strings to upper or lower case before comparing, or by adding

Please Login or Register  to view this content.

as the very first line in the code module.

PS:
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)