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
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
2nd Attempt: this one errors though at [VBA]LRow = ActiveSheet.Range(Rows.Count, "C").End(xlUp).Rows + 1
[/VBA]
[VBA]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[/VBA]
Just guessing here Doug but tryOriginally Posted by YellowLabPro
[VBA]For Each c in ActiveSheet.Range ("C2" & LastRowSrc)
c.Value = Upper(c.Value)
Next[/VBA]
and see what happens.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Another alternative
[VBA]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[/VBA]
or
[VBA]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[/VBA]
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
[vba]
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1
[/vba]
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.... :-) )
[VBA]LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1[/VBA] Gives me a type mismatch
I am trying to state that LRow from C2 down.
Here is the complete procedure:
[VBA]
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" & 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
[/VBA]
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
I have sub in the following in the following code, it actually loops through, but does not change the value to upper case.
[VBA]
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
[/VBA]
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....
Hi Yelp
Preliminaries.
If you are debugging, rather than comment out many lines, use a GoTo to skip over eg
[VBA]GoTo NextBit
MyCode
MyCode
MyCode
MyCode
MyCode
MyCode
NextBit:
Resumes here[/VBA]
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
[vba]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) & ")"
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks MD-
This was awesome!
Is there a difference or benefit here handling it this way rather than commenting, or just more programmer correct?
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....When posting, just delete the commented lines to avid distraction (unless you want us to see them!
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.
This one I have several questions about.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
[vba]
LRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Rows + 1
This should return a number, but it doesn't. Try "Row"
[/vba]
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
[vba]
For Each c In ActiveSheet.Range("C2" & LRow)
If LRow = 12345 then this reads ActiveSheet.Range("C212345")
[/vba]
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
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.
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
[VBA]Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/04/2007 by Malcolm
'
'
Selection.End(xlUp).Select
End Sub
[/VBA]
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
[VBA]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
[/VBA]
re C212345 If you are running Excel 2007, maybe it did change the text in that cell (if you had any)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
BTW, I think too many clarifications in one post. Let's keep them separate for easy reading.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sure thing
[quote=mdmackillop]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.
Was not a typo, done on purpose- I thought it was counting rows, so it needed the collection.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
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?
I often add in a debug.print, msgbox or Add Watch where I see a potential problem or detect an existing one.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
[VBA]
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
' 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
[/VBA]
Select and right click variable. Select Add Watch
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Code with Debug stuff and Immediate/Watch windows
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
