PDA

View Full Version : UCase Help



YellowLabPro
04-07-2007, 09:24 PM
This line does not change the values to upper case. Would someone point out what is wrong please?

For Each c In ActiveSheet.Range("C2" & LastRowSrc)
c.Value = UCase(c.Value)
Next c

YellowLabPro
04-07-2007, 09:38 PM
2nd Attempt: this one errors though at LRow = ActiveSheet.Range(Rows.Count, "C").End(xlUp).Rows + 1


Dim c As Range
LRow = ActiveSheet.Range(Rows.Count, "C").End(xlUp).Rows + 1
For Each c In ActiveSheet.Range("C2" & LRow)
c.Value = UCase(c.Value)
Next c

Aussiebear
04-07-2007, 10:21 PM
This line does not change the values to upper case. Would someone point out what is wrong please?

For Each c In ActiveSheet.Range("C2" & LastRowSrc)
c.Value = UCase(c.Value)
Next c

Just guessing here Doug but try

For Each c in ActiveSheet.Range ("C2" & LastRowSrc)
c.Value = Upper(c.Value)
Next

and see what happens.

Aussiebear
04-07-2007, 10:24 PM
Another alternative
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the range A1:B20
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0

End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces all text to UPPER case
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
On Error GoTo 0

End Sub

Bob Phillips
04-08-2007, 03:27 AM
2nd Attempt: this one errors though at LRow = ActiveSheet.Range(Rows.Count, "C").End(xlUp).Rows + 1

Dim c As Range
LRow = ActiveSheet.Range(Rows.Count, "C").End(xlUp).Rows + 1
For Each c In ActiveSheet.Range("C2" & LRow)
c.Value = UCase(c.Value)
Next c


LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1

YellowLabPro
04-08-2007, 04:20 AM
Hi Guys,
Back at it this morning- Good morning to all.
AussieB- I will try your suggestions after for results. But first trying to see what I am doing wrong so I can grasp structure rules and concept.... object model stuff.

Xld-
Sorry should have put this in post. I had tried that too- only because of parrot reasons- (meaning- repeat what I have seen, not that I understand why Cells instead of Range, just repeating like a sqwaking parrot.... :-) )
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1 Gives me a type mismatch
I am trying to state that LRow from C2 down.

Here is the complete procedure:


Sub A_DataEdited()
Dim LastRow As Long
Dim LastRowSrc As Long
Dim LastRowDst As Long
Dim LRow As Long
Dim i As Long
Dim Ws As Worksheet
Dim arrWS
Dim Cell
Application.ScreenUpdating = True
' arrWS = Array("TGFF", "Fairfax", "TGVB", "Va Beach")
' LastRowDst = 2
'
' With Sheets("DataEdited")
' .Select
' .Cells.ClearContents
' End With
'
' For i = LBound(arrWS) To UBound(arrWS) Step 2
'
' Set Ws = Worksheets(arrWS(i))
' LastRowSrc = Ws.Range("A65536").End(xlUp).Row
'
' Ws.Range("A4:A" & LastRowSrc).Copy Sheets("DataEdited").Range("B" & LastRowDst) 'A>>B = Item Record#
' Ws.Range("D4:D" & LastRowSrc).Copy Sheets("DataEdited").Range("C" & LastRowDst & ":E" & LastRowDst) 'D>> = C:E Item Record Description
' Ws.Range("J4:J" & LastRowSrc).Copy Sheets("DataEdited").Range("F" & LastRowDst) 'J>>F = Inventory Qty.
' Ws.Range("B4:C" & LastRowSrc).Copy Sheets("DataEdited").Range("G" & LastRowDst) 'B:C>>G:H = Department & Category
' Ws.Range("G4:G" & LastRowSrc).Copy Sheets("DataEdited").Range("I" & LastRowDst) 'G>>I = Price
' Sheets("DataEdited").Range("A" & LastRowDst).Resize(LastRowSrc - 3) = arrWS(i + 1)
' LastRowDst = LastRowDst + LastRowSrc - 3
' Next i
'
' LastRow = Range("F" & Rows.Count).End(xlUp).Row
' For Each Cell In Range("G2:I" & LastRow)
' Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
' Next
Dim c As Range
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
For Each c In ActiveSheet.Range("C2" & LRow)
c.Value = UCase(c.Value)
Next c
Range("A1:I1") = Array("Store", "Item#", "OG Records", "~Records", "~Records", "Qty.", "Dept.", "Cat.", "Price")
With Rows("1:1")
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Cells.Columns.AutoFit
End

End Sub


I have the main instructions commented out. I have done this for two reasons, 1) it is a huge process, about two minutes to run, 2) to see if things like this are possible.... meaning commenting out main procedures w/in and code only targets troubled area for testing purposes....

thanks,

YLP

YellowLabPro
04-08-2007, 04:59 AM
I have sub in the following in the following code, it actually loops through, but does not change the value to upper case.


Dim c As Range
LRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row 's + 1
For Each c In ActiveSheet.Range("C" & LRow)
c.Value = UCase(c.Value)
Next c

YellowLabPro
04-08-2007, 05:08 AM
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1


Got it Xld, I missed the extra "s" on .Rows + 1.

Again though, the code runs and loops, but the values are not being changed to Upper Case....

mdmackillop
04-08-2007, 05:08 AM
Hi Yelp
Preliminaries.
If you are debugging, rather than comment out many lines, use a GoTo to skip over eg
GoTo NextBit
MyCode
MyCode
MyCode
MyCode
MyCode
MyCode
NextBit:
Resumes here
When posting, just delete the commented lines to avid distraction (unless you want us to see them!


With regard to your question; basically you have
Dim LRow as Long, c as Range
This is OK

LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
This should return a number, but it doesn't. Try "Row"

For Each c In ActiveSheet.Range("C2" & LRow)
If LRow = 12345 then this reads ActiveSheet.Range("C212345")
so this needs to be ActiveSheet.Range("C2:C" & LRow)

c.Value = UCase(c.Value)
Next c
OK

mdmackillop
04-08-2007, 05:27 AM
Further thoughts
In this case Row + 1 is incorrect as by definition, there is no data in this cell.

An alternative method to specify the range (no better or worse) but sometimes it is benefit to have the end of the range as a range. EG
Dim c As Range, LRow As Range
Set LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp)
For Each c In ActiveSheet.Range("C2", LRow)
c.Value = UCase(c.Value)
Next c

'This allows further use of LRow (although not applicable here) such as
LRow.Offset(1).Formula = "=Sum(C2:" & LRow.Address(0, 0) & ")"

YellowLabPro
04-08-2007, 05:45 AM
Thanks MD-
This was awesome!


Hi Yelp
Preliminaries.
If you are debugging, rather than comment out many lines, use a GoTo to skip over eg
Is there a difference or benefit here handling it this way rather than commenting, or just more programmer correct?


When posting, just delete the commented lines to avid distraction (unless you want us to see them!Ok.... I did intentionally include them in this case so if there was something that was needed that you guys were not scratching your head when everything looked ok....

This next section and the way you broke it out was extremely helpful for me to see what was right and what was incorrect/faulty logic.

With regard to your question; basically you have
Dim LRow as Long, c as Range
This is OK

LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
This should return a number, but it doesn't. Try "Row"

For Each c In ActiveSheet.Range("C2" & LRow)
If LRow = 12345 then this reads ActiveSheet.Range("C212345")
so this needs to be ActiveSheet.Range("C2:C" & LRow)

c.Value = UCase(c.Value)
Next c
OK

This one I have several questions about.

LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
This should return a number, but it doesn't. Try "Row"

1) Returns a number, you are referring to the number of cells from the last cell w/ values, to the first cell w/ an offset of 1? Eg. Range is 12345:2?

2)in the argument we use "Rows", but in the object we use "Row". Guessing here- Is this because Rows are being counted and we want the result for an object?

3) ActiveSeet.Cells- why Cells and not ActiveSheet.Range? I have read that Cells is not an object, rather a property of Range. So this one is not clear yet.

4) Is this equilivant to the previous?
LRow = ActiveSheet.Cells("C" & Rows.Count).End(xlUp).Rows + 1


For Each c In ActiveSheet.Range("C2" & LRow)
If LRow = 12345 then this reads ActiveSheet.Range("C212345")


This one- thanks very nice visual here by the example you offered. Since the argument here is for the object C212345, and the program ran, why did it loop through the range rather than just go to that cell reference?

YLP

YellowLabPro
04-08-2007, 05:53 AM
Further thoughts
In this case Row + 1 is incorrect as by definition, there is no data in this cell.


Ok- what is this cell reference?
I have not figured this concept out yet. Where is this location? This will help if I understand the exact location it is specifing.

Ps. our posts crossed regarding this issue. I referred to it in my 1st question in the previous post, so ignore where appropriate.

mdmackillop
04-08-2007, 06:30 AM
Use of GoTo in debugging.
Why comment out 200 lines of code when you don't need to?


LRow & Rows: simply a typo I think, but in debugging, check that these variables are getting the correct values. In this case with Rows I get an error or 0

To clarify End(xlUp). This is the same as selecting a cell and using Ctrl+Up arrow, in fact if you record a macro, this is exactly what you get
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/04/2007 by Malcolm
'

'
Selection.End(xlUp).Select
End Sub

What you get in using this in code is the Range object which comprises this cell. You then add the property you wish to return
...End(xlUp).Row
...End(xlUp).Address

The Cells is a method of addressing the range from which the end(xlup) is to start.

Try adding different data in column C in sheets 1 & 2 Run the following code from sheets 1, 2 & 3 Can you see the reasons for thye different results
Sub Macro1()
MsgBox Activesheet.Rows.Count
MsgBox Cells(Sheets(1).Rows.Count, "C").End(xlUp).Row
MsgBox Sheets(1).Cells(Rows.Count, "C").End(xlUp).Row
End Sub



re C212345 If you are running Excel 2007, maybe it did change the text in that cell (if you had any)

mdmackillop
04-08-2007, 06:34 AM
BTW, I think too many clarifications in one post. Let's keep them separate for easy reading.

YellowLabPro
04-08-2007, 06:39 AM
BTW, I think too many clarifications in one post. Let's keep them separate for easy reading.

Sure thing

YellowLabPro
04-08-2007, 06:46 AM
Use of GoTo in debugging.
Why comment out 200 lines of code when you don't need to?[quote]
I only do it this way because highlighting it and then hitting the comment button seemed fast and easy to see what I don't want to run. I will try it the other way w/ GoTo.


[quote]LRow & Rows: simply a typo I think, but in debugging, check that these variables are getting the correct values. In this case with Rows I get an error or 0
Was not a typo, done on purpose- I thought it was counting rows, so it needed the collection.

How do I check? Do I place the mouse pointer over the line of code, or is there another way?
If this is the method you are speaking of, I do try this, but a lot of times the intellisense does not show anything so I am never sure what I will find.
Is there another method?

mdmackillop
04-08-2007, 06:57 AM
I often add in a debug.print, msgbox or Add Watch where I see a potential problem or detect an existing one.

YellowLabPro
04-08-2007, 07:04 AM
I really am off-- but one last one for now, and I will check it tonight:
Can you put the debug.print, msgbox in the code where it was failing so I can see what you are referring to?

I have read about the Watch window, but again, one of these things I do not know how to use. If you have a generic worksheet that has one w/ bad code to show me how this works, I would be stoked to see it.


Sub A_DataEdited()
Dim LastRow As Long
Dim LastRowSrc As Long
Dim LastRowDst As Long
Dim LRow As Long
Dim i As Long
Dim Ws As Worksheet
Dim arrWS
Dim Cell
Application.ScreenUpdating = True
' arrWS = Array("TGFF", "Fairfax", "TGVB", "Va Beach")
' LastRowDst = 2
'
' With Sheets("DataEdited")
' .Select
' .Cells.ClearContents
' End With
'
' For i = LBound(arrWS) To UBound(arrWS) Step 2
'
' Set Ws = Worksheets(arrWS(i))
' LastRowSrc = Ws.Range("A65536").End(xlUp).Row
'
' Ws.Range("A4:A" & LastRowSrc).Copy Sheets("DataEdited").Range("B" & LastRowDst) 'A>>B = Item Record#
' Ws.Range("D4:D" & LastRowSrc).Copy Sheets("DataEdited").Range("C" & LastRowDst & ":E" & LastRowDst) 'D>> = C:E Item Record Description
' Ws.Range("J4:J" & LastRowSrc).Copy Sheets("DataEdited").Range("F" & LastRowDst) 'J>>F = Inventory Qty.
' Ws.Range("B4:C" & LastRowSrc).Copy Sheets("DataEdited").Range("G" & LastRowDst) 'B:C>>G:H = Department & Category
' Ws.Range("G4:G" & LastRowSrc).Copy Sheets("DataEdited").Range("I" & LastRowDst) 'G>>I = Price
' Sheets("DataEdited").Range("A" & LastRowDst).Resize(LastRowSrc - 3) = arrWS(i + 1)
' LastRowDst = LastRowDst + LastRowSrc - 3
' Next i
'
' LastRow = Range("F" & Rows.Count).End(xlUp).Row
' For Each Cell In Range("G2:I" & LastRow)
' Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
' Next
Dim c As Range
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
For Each c In ActiveSheet.Range("C2" & LRow)
c.Value = UCase(c.Value)
Next c
Range("A1:I1") = Array("Store", "Item#", "OG Records", "~Records", "~Records", "Qty.", "Dept.", "Cat.", "Price")
With Rows("1:1")
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Cells.Columns.AutoFit
End

End Sub

mdmackillop
04-08-2007, 07:19 AM
Select and right click variable. Select Add Watch

mdmackillop
04-08-2007, 07:21 AM
Code with Debug stuff and Immediate/Watch windows

mdmackillop
04-08-2007, 07:31 AM
If you have a generic worksheet that has one w/ bad code
:bat2:

Bob Phillips
04-08-2007, 08:59 AM
Select and right click variable. Select Add Watch Select LRow in the code pane and just drag it to the Watch window ... much quicker.

mdmackillop
04-09-2007, 02:08 AM
Thanks Bob,
I'll remember that one. Now I've got time for :coffee: