Posted By

gritter55 on 01/18/11


Tagged

vba


Versions (?)

TextToColumns


 / Published in: Visual Basic
 

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Dim buffer As String
  3.  
  4. If (Target.Column = 14) Then
  5. Select Case Target.row
  6. Case 47 'Calplate barcode
  7. buffer = Cells(47, 14).Text
  8. Sheets("Barcode Data").Range("B14") = buffer
  9. Application.DisplayAlerts = False
  10. Sheets("Barcode Data").Range("B14").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  11. Comma:=True, Space:=False
  12. Application.DisplayAlerts = True
  13.  
  14. Case 48 'Diluent barcode
  15. buffer = Cells(48, 14).Text
  16. Sheets("Barcode Data").Range("B20") = buffer
  17. Application.DisplayAlerts = False
  18. Sheets("Barcode Data").Range("B20").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  19. Comma:=True, Space:=False
  20. Application.DisplayAlerts = True
  21.  
  22. Case 49 'Range A barcode
  23. buffer = Cells(49, 14).Text
  24. Sheets("Barcode Data").Range("B32") = buffer
  25. Application.DisplayAlerts = False
  26. Sheets("Barcode Data").Range("B32").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  27. Comma:=True, Space:=False
  28. Application.DisplayAlerts = True
  29.  
  30. Case 50 'Range B barcode
  31. buffer = Cells(50, 14).Text
  32. Sheets("Barcode Data").Range("B38") = buffer
  33. Application.DisplayAlerts = False
  34. Sheets("Barcode Data").Range("B38").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  35. Comma:=True, Space:=False
  36. Application.DisplayAlerts = True
  37.  
  38. Case 51 'Range C barcode
  39. buffer = Cells(51, 14).Text
  40. Sheets("Barcode Data").Range("B44") = buffer
  41. Application.DisplayAlerts = False
  42. Sheets("Barcode Data").Range("B44").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  43. Comma:=True, Space:=False
  44. Application.DisplayAlerts = True
  45.  
  46. Case 52 'Range D barcode
  47. buffer = Cells(52, 14).Text
  48. Sheets("Barcode Data").Range("B50") = buffer
  49. Application.DisplayAlerts = False
  50. Sheets("Barcode Data").Range("B50").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  51. Comma:=True, Space:=False
  52. Application.DisplayAlerts = True
  53.  
  54. Case 53 'Range E barcode
  55. buffer = Cells(53, 14).Text
  56. Sheets("Barcode Data").Range("B56") = buffer
  57. Application.DisplayAlerts = False
  58. Sheets("Barcode Data").Range("B56").TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=False, _
  59. Comma:=True, Space:=False
  60. Application.DisplayAlerts = True
  61.  
  62. Case Else
  63. ' nothing
  64. End Select
  65. End If
  66. End Sub

Report this snippet  

You need to login to post a comment.