Excel 2016 - VBA - Problem - automatically converting text in column to Uppercase

TheShyButterfly

TheShyButterfly

New Member
Joined
Nov 6, 2011
Messages
43
Hi there,
Thank you for viewing my problem.

I am having particular difficulty in getting code to automatically change text in a specific column. I'd like the code to change the case either as the user types, or after the user leaves the cell.

Actually, I would ideally like to have:
  • All text in Column C to automatically convert to Proper case
  • All text in Column D to automatically convert to Uppercase
  • Concatenate the names into Column E

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]First Name (Propercase)[/TD]
[TD]Last Name (Uppdercase)[/TD]
[TD]Full Name (auto concatenated)[/TD]
[TD]PPort #
[/TD]
[TD]Date Issued
[/TD]
[TD]Date Expires
[/TD]
[TD]SAC#[/TD]
[TD]SAC Details[/TD]
[TD]Site Type
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


I have tried several codes which don't seem to work. I put the code into the Worksheet module (code name Sheet2)
- Note: I don't use the sheet tab names in my projects because users change the sheet name all the time.

I was hoping to get one of the codes to work for 1 column (C) (Proper case) and then add a secondary line of code in for the Uppercase into the other column (D) and then find a way to automatically concatenate the two names together in column E. But I have failed and I can't work out why.

These are the links to the codes I have tried ....

Below is some of the combinations of code I tried:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim cell As Range
    Application.ScreenUpdating = False
     Application.EnableEvents = False

    For Each cell In Target
        Select Case cell.Column
            Case 4 ' column numbers eg. 10, 11, 12
                cell = UCase(cell)

        End Select
      Application.EnableEvents = True

    Next cell

End Sub


and this one ...

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   Dim cell As Range
    Application.ScreenUpdating = False
     Application.EnableEvents = False

    For Each cell In Target
        Select Case cell.Column
            Case 4 ' column numbers eg. 10, 11, 12
                cell = UCase(cell)

        End Select
      Application.EnableEvents = True

    Next cell
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column.Count = 4 Then
    Target.Value = UCase(Target.Value)
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
 
 
Application.EnableEvents = False
 
If Target.Column = 3 Then
 
Target = StrConv(Target, vbProperCase)
 
End If
 
Application.EnableEvents = True

End Sub

The only other code I have in this workbook, is the Vlookup for looking up the SAC# ... the code is unfortunately a 'macro button' ... it would be nice if it just looked up the values and updated the cells when user clicks outside the column/cell.
Anyhow, this is the code for the Vlookup ... (even though I'm not sure that it would interfere with the case coding ... but then I'm still on a VBA learning curve).

The named range: SAC_Details
Field range: =Locations!$C:$AD

Code: 
Option Explicit

Sub lookup1()

     Dim ws1 As Worksheet
     Dim x As Long

     Set ws1 = Sheet2


     Sheet2.Select
          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data

     Range("J" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 2, 0)

          Next

          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data
     Range("K" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 16, 0)

          Next

End Sub


I really appreciate your assistance :)
Thank you in advance.

Cheers,
The ShyButterfly
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try this Change event:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    ' ignore entire row/column changes as may be inserting/deleting
    If Target.Rows.Count = Rows.Count Or Target.Columns.Count = Columns.Count Then Exit Sub

    On Error GoTo clean_exit

    ' check if cells in column c or d are changed
    If Not Intersect(Target, Range("C:D")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each cell In Intersect(Target, Range("C:D")).Cells
            Select Case cell.Column
                Case 3
                    cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                Case 4
                    cell.Value = UCase(cell.Value)
            End Select
            Cells(cell.Row, "E").FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
        Next cell
    End If
    
clean_exit:
    Application.EnableEvents = True

End Sub
 
Upvote 0
Hi Rory .... Wow ... this is fantastic :) Thank you so much for your quick response ... it works a charm :)

I was also wondering about the Vlookup Macro which I mentioned below ....

The only other code I have in this workbook, is the Vlookup for looking up the SAC# ... the code is unfortunately a 'macro button' ... it would be nice if it just looked up the values and updated the cells when user clicks outside the column/cell.
Anyhow, this is the code for the Vlookup ... (even though I'm not sure that it would interfere with the case coding ... but then I'm still on a VBA learning curve).

The named range: SAC_Details
Field range: =Locations!$C:$AD

Code:
Option Explicit

Sub lookup1()

Dim ws1 As Worksheet
Dim x As Long

Set ws1 = Sheet2


Sheet2.Select
For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data

Range("J" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 2, 0)

Next

For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data
Range("K" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 16, 0)

Next

End Sub
Click to expand...

The code for the Vlookup is linked to a button (I had no choice, because I couldn't get the macro to run in the worksheet module ... so I had to create a button to assign it to .... but I'm trying to get a streamlined workflow where the user has to do the minimum amount of clicking etc... .)

Is there any reason in the code why it shouldn't be successfully called via the worksheet module?
There doesn't seem to be a way to upload the actual workbook so that you can really see what is/is not happening... so I hope you can make sense of what I've provided.

Thank you VERY much again .... I remember you were instrumental in helping me out a few times over the last few years and I am so grateful .. :)
Cheers,
TheShyButterfly
 
Upvote 0
There doesn't seem much point in processing all the rows every time, so this version will just process the ones being changed. If the SAC# is not matched, it will clear the cells in J and K.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    ' ignore entire row/column changes as may be inserting/deleting
    If Target.Rows.Count = Rows.Count Or Target.Columns.Count = Columns.Count Then Exit Sub

    On Error GoTo clean_exit

    ' check if cells in column c or d are changed
    If Not Intersect(Target, Range("C:D,I:I")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Dim cell As Range

        For Each cell In Intersect(Target, Range("C:D")).Cells
        
            Select Case cell.Column
                Case 3 ' col C
                    cell.Value = Application.WorksheetFunction.Proper(cell.Value)
                Case 4 ' col D
                    cell.Value = UCase(cell.Value)
                Case 9 ' col I
                
                    Dim lookupRange As Range
                    Set lookupRange = Application.Range("SAC_Details")
                    
                    Dim rwMatch
                    rwMatch = Application.Match(cell.Value, lookupRange.Columns(1), 0)
                    
                    If Not IsError(rwMatch) Then
                        Cells(cell.Row, "J").Value = lookupRange.Cells(rwMatch, 2).Value
                        Cells(cell.Row, "K").Value = lookupRange.Cells(rwMatch, 16).Value
                    Else
                        Cells(cell.Row, "J").Resize(, 2).Value = vbNullString
                    End If
                    
            End Select
            Cells(cell.Row, "E").FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
        Next cell
        
    End If
    
clean_exit:
    Application.EnableEvents = True

End Sub
 
Upvote 0
Thank you again Rory, :smile:
I deleted all the code of the Worksheet module, and copied all the code from your latest post.
Your code relating to the text cases works perfectly, however, the code for the SAC# is not working, when I enter the 4 digit number, which I know exists, it doesn't do anything.
I've double/triple checked the Named Range and it is correct.

The results from the lookup of column I (eg. 7000, 7001, etc...) should return the values of the named range in column 2(Col J) and column 16 (Col K), which from what you have in the code is correct.

The only thing I am confused about is the following code, because I'm not familiar with the 'Match' function in VBA ... so I can't try to work out what it is looking to match, and which column data it is returning.
I presume it is doing the more 'efficient' way of the usual Excel function of Vlookup (Vlookup($I8,SAC_Details,2,0) and Vlookup($I8,SAC_Details,16,0) ...obviously for each row that Column I will be populated with the 4 digit number, and returning the results into column J and K.

Is there some easy/ier way to explain why your code isn't working? I have blocked out the code that was in the Macro Module, so it wouldn't interfere with your code.

Sorry to be a pain .... thank you for your patience :)

Cheers,
TheShyButterfly
 
Upvote 0
Does a VLOOKUP formula in the sheet work? My guess would be not, as I suspect you have a mismatch between numbers and numbers stored as text.
 
Upvote 0
Hi Rory,
I tried with the normal Excel function, =VLOOKUP(I9,SAC_Details,2,0) and it worked perfectly.

The code I provided that I had to put into the Macro Module, also worked when I assigned it to a button, it also worked perfectly ... I just wanted it to do it in the worksheet module ... not by pushing the button.

Code: 
Sub lookup1()

     Dim ws1 As Worksheet
     Dim x As Long

     Set ws1 = Sheet2


     Sheet2.Select
          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data

     Range("J" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 2, 0)

          Next

          For x = 1 To Range("C100000").End(xlUp).Row - 7 '7 is the number of rows before the start of the data
     Range("K" & x + 7).Value = WorksheetFunction.VLookup(Range("I" & x + 7).Value, Range("SAC_Details"), 16, 0)

          Next

End Sub

I know it's strange ... but that isn't unusual for me ... unfortunately.

I unblocked the Vlookup1 code in the Macro Module.... and tested it with the button ... it populated the 2 columns with the correct data :)

What can I do to help you help me resolve this ? :)

Cheers,
TheShyButterfly
 
Upvote 0
Can you try editing this part of the code:

Code: 
Else
                        Cells(cell.Row, "J").Resize(, 2).Value = vbNullString

to this:

Code: 
Else
                        Cells(cell.Row, "J").Resize(, 2).Value = "No match"

just to be sure it's actually running?
 
Upvote 0
Hi Rory,

I replaced the code
Code: 
Cells(cell.Row, "J").Resize(, 2).Value = vbNullString


with

Code: 
Cells(cell.Row, "J").Resize(, 2).Value = "No match"

Unfortunately, this didn't work ... I entered the same number as I had before using the Macro button, but blocking that code and editing your code as requested, this didn't do anything ... i moved from the SAC# column (after entering the number) and moved to another cell and it remained empty.

I checked the 'Compile VBAproject' and received no errors. I tried to 'step through' the code, but I was unable to step through the code ...

I'm about to head to bed now ... (1am), but I'll return after work tomorrow ... in case you think of something while I'm sleeping :)
Thank you so much again for your time and effort to resolve this issue :)
Good night for now.
Cheers,
TheShyButterfly
 
Upvote 0
I've just spotted the issue - stupid error on my part! This line:

Rich (BB code): 
For Each cell In Intersect(Target, Range("C:D")).Cells

needs to be:

Rich (BB code): 
For Each cell In Intersect(Target, Range("C:D,I:I")).Cells
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
207
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
102
Fluff
Fluff
K
  • Solved
Undo Stack gets cleared running VBA - Possible Solution to fix this?
Replies
5
Views
165
kellman
K
J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
180
jolivanes
jolivanes
I
  • Question Question
VBA to detect hidden rows. then Re-hide
Replies
7
Views
208
Skyybot
Skyybot

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top