• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CHANGE CASE function

When you post VBA code, use the button found in thread menu.
upload_2017-6-14_14-35-11.png

Which will bring up...
upload_2017-6-14_14-35-40.png

VBA code should go in there.
 
Code:
Sub ChangeCase()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Formula Info" Then

'Change it to your sheet name or you can use sheet index# instead of Name.

With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 1).End(xlUp).Row - 2)
  .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
  End With
  End If
Next
End Sub

FINALLY, got sanitized file with correct error. :)
 

Attachments

  • Chandoo - TABBED Invoice.xlsm
    591.7 KB · Views: 10
Last edited:
Hi ,

Change as follows :

With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)

In your code , the 5 is 1 , and column A has no data , which is why the error is being generated.

Narayan
 
If it's really just Y and N to convert, why not use Find and Replace (Ctrl+H)?
Ctrl + H is a good suggestion; however, I need to make sure I highlight only columns E and F and many of the sheets (tabs) have several thousand rows...but yes, it could still be done by highlighting just the columns.
VBA makes it less tedious.
 
Hi ,

Change as follows :

Hi ,

Change as follows :

With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)

In your code , the 5 is 1 , and column A has no data , which is why the error is being generated.

Narayan
So the 5 tells VBA to start in the 5th column which is E, correct?

Will this VBA do all 19 sheets (tabs), or will it only do the current sheet (tab) I'm looking at? I know I could experiment and figure this out, but I thought you would know by looking at the code. Based on the IF statement, and as a guess, it will do all 19 (or however many) sheets with one execution of the RUN command, correct?

(I've never had any training on VBA, but have had some BASIC programming experience long ago.)

In your code , the 5 is 1 , and column A has no data , which is why the error is being generated.

Narayan
THANK YOU! Good eye.
 
Hi ,

Change as follows :

With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)

In your code , the 5 is 1 , and column A has no data , which is why the error is being generated.

Narayan
It works in the sanitized file, but still getting an error 400 in the "real" file.
May I send you privately the real file?

upload_2017-6-16_0-42-44.png
 
Hi ,

Sure. In case you cannot upload it in a PM , send it to me on :

tech [dot] support [at] chandoo [dot] org

Narayan
Narayan,

Of course, once you get spoiled by what VBA can do (A HUGE THANKS, BY THE WAY), you start to see and fantasize about what else VBA can do to save time and catch human errors.

How difficult would it be to add "remove leading blanks" in Columns A through D using the same Change Case VBA? ...or would it be better to create a new one?
I assumed that just adding to Change Case VBA would have the "Error 400" fix already in place.

Code:
Sub ChangeCase()
  Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Formula Info" Then
'  Change "Formula Info" sheet name or you can use sheet index# instead of name.

  If ws.Cells(Rows.Count, 5).End(xlUp).Row > 2 Then
'  This line was added to "fix" Error 400 which was caused by blank tab(s).
'  The  Next Tech tab has no data, and its header is in row 2.
'  The part which returns the last row of data is:  ws.Cells(Rows.Count, 5).End(xlUp).Row
'  which will return 3 or more as long as the worksheet has data; thus subtracting 2 from this value returns any number from 1 upwards.
'  However, when the worksheet has no data, the above code will return 2, and subtracting 2 from this = 0, which is an invalid row number in Excel.
'  To check this, we test whether the value returned by the above code is greater than 2; if so, we proceed further, else we exit.


  With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
'  E3:F3 tells which row and column to start.  (Rows.Count, 5) tells which column to start in.

  .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
  End With
  End If
  End If
  Next
End Sub
 

Attachments

  • Chandoo - Change Case and TRIM leading blanks.xlsm
    220.7 KB · Views: 1
Last edited:
Hi ,

Try this :
Code:
Sub ChangeCase()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Formula Info" Then
'          Change "Formula Info" sheet name or you can use sheet index# instead of name.
          If ws.Cells(Rows.Count, 5).End(xlUp).Row > 2 Then
              With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
                  .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
                  With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
                        .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(#),"""")", "#", .Address))
                  End With
              End With
          End If
        End If
    Next
End Sub
Narayan
 
Hi ,

Try this :
Code:
Sub ChangeCase()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Formula Info" Then
'          Change "Formula Info" sheet name or you can use sheet index# instead of name.
          If ws.Cells(Rows.Count, 5).End(xlUp).Row > 2 Then
              With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
                  .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
                  With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
                        .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(#),"""")", "#", .Address))
                  End With
              End With
          End If
        End If
    Next
End Sub
Narayan


Would the following enhancement work with CLEAN to cover non-printable characters, represented by the ASCII numeric codes 0 to 31 similarly to
e.g. =TRIM(CLEAN(A1))

Code:
    With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))
    End With

I'm going to give it a try....otherwise I assume I'll have to do the following:
Code:
    With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(#),"""")", "#", .Address))
    End With
    With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",CLEAN(#),"""")", "#", .Address))
    End With
 
Last edited:
Hi ,

It should work even when you combine both functions in one IF formula.

Narayan
I tried combining them:
Code:
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))
    End With
TRIM worked, CLEAN did not.

I reversed them and tried:
Code:
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",CLEAN(TRIM(#)),"""")", "#", .Address))
    End With
TRIM worked, CLEAN did not.

I tried running them separately:
Code:
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",CLEAN(#),"""")", "#", .Address))
End With

With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
       .Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(#),"""")", "#", .Address))
    End With
TRIM worked, CLEAN did not.

Any ideas...suggestions?
 
Hi ,

I tried it in your file , using a combination of CLEAN and TRIM , and it worked removing both leading and trailing spaces , as well as several characters with ASCII codes between 1 and 31.

Narayan
Which version? The combined (see immediately below):
Code:
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))

Maybe I'm not creating correct ASCII codes between 1 and 31?
I'm using ALT + (num pad 0-31)
Please see attached file.
 

Attachments

  • Chandoo - Change Case and TRIM leading blanks.xlsm
    320.8 KB · Views: 3
Last edited:
Hi ,

The procedure to enter the ASCII codes between 1 and 31 is correct , but when I isolate the characters that have been entered in a cell , and check out their ASCII code using the CODE function , it shows 63 , which is what will be displayed if the code is greater than the ASCII limit of 255.

I doubt that these are characters in the range 1 to 31.

However , strangely , I am not able to reproduce the behaviour I observed yesterday when I commented that a combination of CLEAN and TRIM had worked at my end. Now this combination does not , neither do the two functions work individually ; TRIM does not remove the extra spaces !

I am at a loss to understand what is happening.

I tried using these same formulae in worksheet cells by manually entering them , and their behaviour is the same ; TRIM just does not remove leading and trailing spaces.

I'll keep trying to find a solution.

Narayan
 
Hi ,

The problem was that there were characters with an ASCII code of 160 , which will not be removed by either TRIM or CLEAN.

Once these characters were manually removed , the combination of CLEAN and TRIM , in any order , did the job.

Narayan
 
Hi ,

The problem was that there were characters with an ASCII code of 160 , which will not be removed by either TRIM or CLEAN.

Once these characters were manually removed , the combination of CLEAN and TRIM , in any order , did the job.

Narayan
Whew! Thanks for figuring it out. I was beginning to wonder if my Excel 2007 had finally bit the dust.

...and you brought up another good point...
It shouldn't matter whether it's standard spreadsheet use or VBA use,
=TRIM(CLEAN(A1)) and =CLEAN(TRIM(A1)) should not make a difference, correct?
 
Interestingly en
Hi ,

The problem was that there were characters with an ASCII code of 160 , which will not be removed by either TRIM or CLEAN.

Once these characters were manually removed , the combination of CLEAN and TRIM , in any order , did the job.

Narayan
I believe you and have no doubt about you finding ALT + 160, etc., but interestingly enough, I only used ALT + (0 through 31) to create the symbols for testing; therefore, ACSII code 160 (and other ASCIIs over 31) should not have been in there...go figure. :rolleyes:
 
Whew! Thanks for figuring it out. I was beginning to wonder if my Excel 2007 had finally bit the dust.

...and you brought up another good point...
It shouldn't matter whether it's standard spreadsheet use or VBA use,
=TRIM(CLEAN(A1)) and =CLEAN(TRIM(A1)) should not make a difference, correct?
Hi ,

Absolutely ; both are equivalent , and will produce the same result(s).

Narayan
 
Back
Top