PDA

View Full Version : Solved: uCase, cut and paste



khalid79m
02-20-2009, 05:06 AM
I have taken over a piece of work from a collegue who has left the business, in his script he has this bit of code

Dim rCell As Range

For Each rCell In Sheets("CallData").Range("Final_Check").Cells

If UCase(rCell.Value) = "Complete" Then
rCell.EntireRow.Cut Sheets("SubmittedData").Range("A" & Sheets("SubmittedData"). _
Range("A" & Rows.Count).End(xlUp).Row + 1)
End If

Next

This code is designed to look at all cells in the range "Final_Check" and if any cells contain the words "Complete" , then to copy that entire row and paste it into submitteddata in the next avaliable row using column a to identify the next available row. I tweaked the code as it required a change and caput in no longer works :( what have i DONE?:doh:

Paul_Hossler
02-20-2009, 05:15 AM
UCase(rCell.Value) = "Complete"


My guess is that this would never be true, since what ever is in rCell would always be UC, and "Complete" would never be UC

Try

UCase(rCell.Value) = "COMPLETE"


There is an Option Compare Text setting on a module. I've never used it, but I have read here abouti it. Was it used?



Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale (http://www.vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01200929.htm). When the same characters are sorted using Option Compare Text, the following text sort order is produced:
(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Paul

khalid79m
02-20-2009, 05:46 AM
THANKS you right eventhough the range has it displayed in lowercase, in the code if i put it in upper case it worked, genuis