CODEWITHSUNDEEP
excelvba
Ben Logan
Ben Logan

Reputation: 187

Code Was Working - No Changes, Now Not Working (VBA)

I'm stumped - this code was working fine - now, it works in the wrong columns. Take column L for example, it's coded to convert the selected cell into Propercase but now it converts to Uppercase. Column I code gets ignored entirely.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup
Application.EnableEvents = False: Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual

   'converts staff codes into uppercase
   If Not (Application.Intersect(Target, Me.UsedRange.Columns("K")) Is Nothing) And Not Target.Row = 15 Then
        Target.Value2 = UCase$(Target.Value2)
   End If

   'converts Rep Codes into uppercase
     If Not (Application.Intersect(Target, Me.UsedRange.Columns("J")) Is Nothing) And Not Target.Row = 15 Then
        Target.Value2 = UCase$(Target.Value2)
   End If

   'converts Staff Names into proper case,
   If Not (Application.Intersect(Target, Me.UsedRange.Columns("L")) Is Nothing) And Not Target.Row = 15 Then
        Target.Value2 = StrConv(Target.Value2, vbProperCase)
   End If

   'converts staff type into capitals.
   If Not (Application.Intersect(Target, Me.UsedRange.Columns("I")) Is Nothing) And Not Target.Row = 15 Then
         Target.Value2 = UCase$(Target.Value2)
   End If

   'converts store code into uppercase
    If Not (Application.Intersect(Target, Range("STORE_CODE")) Is Nothing) Then
        Target.Value2 = UCase$(Target.Value2)
   End If

      'converts store name into propercase
   If Not (Application.Intersect(Target, Range("STORE_NAME")) Is Nothing) Then
        Target.Value2 = StrConv(Target.Value2, vbProperCase)
   End If

   'copy pay value one cell over into hidden column
   If Not (Application.Intersect(Target, Me.UsedRange.Columns("G")) Is Nothing) Then
          Target.Offset(0, 1).Value2 = Target.Value2
          Target.Value2 = ""
   End If
Cleanup:
Application.EnableEvents = True: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic ' etc..
End Sub

Upvotes: 0

Views: 75

Answers (1)

Axel Richter
Axel Richter

Reputation: 61945

The Me.UsedRange.Columns("L") in your code means the Lth, which is the twelfth, column of your UsedRange. If the UsedRangestarts in column A, then this is column L. But if the UsedRange starts in column B, then the twelfth column in UsedRange is column M.

Example:

Sub test()
 Dim oRange As Range

 Set oRange = ActiveSheet.Range("A1:Z100")
 MsgBox oRange.Columns("L").Address 'L1:L100

 Set oRange = ActiveSheet.Range("B1:Z100")
 MsgBox oRange.Columns("L").Address 'M1:M100

End Sub

Why the usage of UsedRange in your code there?

Upvotes: 2

Related Questions