Eloise T
Active Member
"CODE" tag?Upload sample workbook where it produces that error. I've never seen it before.
P.S. Please use CODE tag.
"CODE" tag?Upload sample workbook where it produces that error. I've never seen it before.
P.S. Please use CODE tag.
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
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.If it's really just Y and N to convert, why not use Find and Replace (Ctrl+H)?
THANK YOU! Good eye.Hi ,
Change as follows :
So the 5 tells VBA to start in the 5th column which is E, correct?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
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
It works in the sanitized file, but still getting an error 400 in the "real" file.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
Hi ,It works in the sanitized file, but still getting an error 400 in the "real" file.
May I send you privately the real file?
View attachment 42546
Narayan,Hi ,
Sure. In case you cannot upload it in a PM , send it to me on :
tech [dot] support [at] chandoo [dot] org
Narayan
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
I understand. Thank you.Hi ,
It is late tonight , and I can reply only tomorrow.
Narayan
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
Hi ,
Try this :
NarayanCode: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
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(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
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
.Value = .Parent.Evaluate(Replace("IF(#>"""",CLEAN(#),"""")", "#", .Address))
End With
I tried combining them:Hi ,
It should work even when you combine both functions in one IF formula.
Narayan
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))
End With
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
.Value = .Parent.Evaluate(Replace("IF(#>"""",CLEAN(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
With ws.[A3:D3].Resize(ws.Cells(Rows.Count, 5).End(xlUp).Row - 2)
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(#),"""")", "#", .Address))
End With
Which version? The combined (see immediately below):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
.Value = .Parent.Evaluate(Replace("IF(#>"""",TRIM(CLEAN(#)),"""")", "#", .Address))
Whew! Thanks for figuring it out. I was beginning to wonder if my Excel 2007 had finally bit the dust.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.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
It took me a while as my eyes passed over the symbol dozens of times, but...I finally...When you post VBA code, use the button found in thread menu.
View attachment 42524
Which will bring up...
View attachment 42525
VBA code should go in there.
Hi ,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?