Macro Question

C

carl

I am using this code:
______________________________________________________-

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value
Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

___________________________________________________

This worked great until we started using upper and lower case
characters in one of the data fields. For example, my data table now
looks like this:


Code Number
HO 549
HO 910
HO 919
HO 980
HO 918
HO 937
HO 051
ho 549
ho 910
HO 642

When I run the code above, it produces this result:

HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980

I am looking for this result:

HO 051;549;642;910;918;919;937;980
ho 549;910

Is there a way to modify the code to produce the desired result ?

If not, is there code that can produce the desired result ?

Thank you in advance.
 
J

Jim Cone

Add... Option Compare Text
as the second line in your module - right after Option Explicit.
--
Jim Cone
Portland, Oregon USA
Special Sort Excel add-in...
http://www.contextures.com/excel-sort-addin.html

..
..
..

"carl" <[email protected]>
wrote in message
I am using this code:
______________________________________________________-

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

___________________________________________________

This worked great until we started using upper and lower case
characters in one of the data fields. For example, my data table now
looks like this:


Code Number
HO 549
HO 910
HO 919
HO 980
HO 918
HO 937
HO 051
ho 549
ho 910
HO 642

When I run the code above, it produces this result:

HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980

I am looking for this result:

HO 051;549;642;910;918;919;937;980
ho 549;910

Is there a way to modify the code to produce the desired result ?
If not, is there code that can produce the desired result ?
Thank you in advance.
 
C

carl

Add...  Option Compare Text
as the second line in your module - right after Option Explicit.
--
Jim Cone
Portland, Oregon  USA
Special Sort Excel add-in...http://www.contextures.com/excel-sort-addin.html

.
.
.

"carl" <[email protected]>
wrote in messageI am using this code:
______________________________________________________-

Option Explicit
Sub testme()

    Dim iRow As Long
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim wks As Worksheet

    Set wks = Worksheets("Sheet1")

    With wks
        FirstRow = 2 'headers in row 1
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Range("A1:C" & LastRow).Sort _
            key1:=.Range("A1"), order1:=xlAscending, _
            key2:=.Range("c1"), order2:=xlAscending, _
            key3:=.Range("b1"), order3:=xlAscending, _
            header:=xlYes

        For iRow = LastRow To FirstRow + 1 Step -1
            If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
             And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value Then
                .Cells(iRow - 1, "B").Value _
                   = .Cells(iRow - 1, "B").Value _
                          & ";" & .Cells(iRow, "B").Value
                .Rows(iRow).Delete
            End If
        Next iRow
    End With

End Sub

___________________________________________________

This worked great until we started using upper and lower case
characters in one of the data fields. For example, my data table now
looks like this:

Code Number
HO 549
HO 910
HO 919
HO 980
HO 918
HO 937
HO 051
ho 549
ho 910
HO 642

When I run the code above, it produces this result:

HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980

I am looking for this result:

HO 051;549;642;910;918;919;937;980
ho 549;910

Is there a way to modify the code to produce the desired result ?
If not, is there code that can produce the desired result ?
Thank you in advance.
Click to expand...

Thank you John.

I tried running the code with the change:

Option Explicit
Option Compare Text

Sub testme()


Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet


Set wks = Worksheets("Sheet1")


With wks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


.Range("A1:C" & LastRow).Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("c1"), order2:=xlAscending, _
key3:=.Range("b1"), order3:=xlAscending, _
header:=xlYes


For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value _
And .Cells(iRow, "C").Value = .Cells(iRow - 1, "C").Value
Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ";" & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With


End Sub


I got this result:

HO 051;549;549;642;910;910;918;919;937;980


It looks lke the macro combined HO and ho ?

I was trying to get this result:

HO 051;549;642;910;918;919;937;980
ho 549;910
 
J

Jim Cone

Yes, it changed the comparison from Case sensitive to not Case sensitive.
Have you tried sorting your data before running your original code?
'--
Jim Cone



"carl"
<[email protected]>
wrote in message

I tried running the code with the change:
Option Explicit
Option Compare Text
-snip-

I got this result:
HO 051;549;549;642;910;910;918;919;937;980

It looks lke the macro combined HO and ho ?
I was trying to get this result:

HO 051;549;642;910;918;919;937;980
ho 549;910
 
C

carl

Yes, it changed the comparison from Case sensitive to not Case sensitive.
Have you tried sorting your data before running your original code?
'--
Jim Cone

"carl"
<[email protected]>
wrote in message
I tried running the code with the change:
Option Explicit
Option Compare Text
-snip-

I got this result:
HO 051;549;549;642;910;910;918;919;937;980

It looks lke the macro combined HO and ho ?
I was trying to get this result:

HO 051;549;642;910;918;919;937;980
ho 549;910





- Show quoted text -
Click to expand...

Thanks Again John.

I did sort on Col A and ran the original code and got the same result:

HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980


I am looking for this result:


HO 051;549;642;910;918;919;937;980
ho 549;910


Any thoughts ?
 
J

Jim Cone

I replaced all instances of "ho" with ho and a space... "ho " (looks the same, but is not)
Your code then produced the results you want.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"carl" <[email protected]>
wrote in message
Yes, it changed the comparison from Case sensitive to not Case sensitive.
Have you tried sorting your data before running your original code?
'--
Jim Cone

"carl"
<[email protected]>
wrote in message
I tried running the code with the change:
Option Explicit
Option Compare Text
-snip-

I got this result:
HO 051;549;549;642;910;910;918;919;937;980

It looks lke the macro combined HO and ho ?
I was trying to get this result:

HO 051;549;642;910;918;919;937;980
ho 549;910





- Show quoted text -
Click to expand...

Thanks Again John.

I did sort on Col A and ran the original code and got the same result:

HO 051;549
ho 549
HO 642;910
ho 910
HO 918;919;937;980


I am looking for this result:


HO 051;549;642;910;918;919;937;980
ho 549;910


Any thoughts ?
 
D

Dave Peterson

I use xl2003, so the menu options may be slightly different.

But you can record a macro when you're sorting some test data (in a test worksheet).

Put some test data in column A (upper and lower case)
Select column A
Data|Sort (in xl2003 menus)
Click the Options Button on the sort dialog.

You'll see a checkbox for "Case sensitive". Check this box.

The recorded code looks like:

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

You'll see that "matchcase:=true" parm. Include that little bit into your
code's sort statement and you'll be close to what you want!
 
J

Jim Cone

Pete,
There is much I don't understand about a lot of the questions/responses posted here.
Some of my past posts, I'm convinced were done while I was asleep or drunk. <g>
I am just happy to get a response as the count of unique non responders, to my posts, is now at 1700.
I assume "John" is a translation issue.
Jim Cone


Jim, why does the OP keep calling you John ??
Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify A Macro To Recognize Case Sensitive 3
Insert new ROW if cell equals value 6
Macro do works in excel... 7
Run time Error - Application Defined or Object Defined Error Excel Vba 0
Insert rows by group and copy value 2
Create a yes no message on a command button on click 0
Insert Row based on count and copy range to inserted rows 2
Save details 2

Top