Best way to find the right column of data from a Sub

[JenniferMurphy]

I have a bunch of sheets with columns of data. Two of the columns contain ratings, such as "Mint", "Near Mint", "Very Good Plus", "Very Good", etc. There are 9 ratings in all.

As they are, those columns can only be sorted alphabetically, which is not that helpful. My solution is to write a macro that will find those columns and add a number prefix that will allow useful sorting. The conversions are:

I have 2 questions:

1. I know I can define those columns as a named range, but that's a pain to have to do every time I create a new sheet. Is there a way I can have the Sub scan the first couple of rows. If it finds one of the old terms, it will then convert it and then scan down that column until it runs out of data. (Several hundred rows.)

2, Is there a better way to do the conversion that a Case statement:

Select Case UCase(pOld)
  Case "MINT":            CvtText = "1 Mint"
  Case "NEAR MINT":       CvtText = "2 Mint-"
  Case "VERY GOOD PLUS":  CvtText = "3 VGood+"
  Case "VERY GOOD":       CvtText = "4 VGood"
  Case "GOOD PLUS":       CvtText = "5 Good+"
  Case "GOOD":            CvtText = "6 Good"
  Case "FAIR":            CvtText = "7 Fair"
  Case "POOR":            CvtText = "8 Poor"
  Case "NO COVER":        CvtText = "9 None"
  Case Else:              CvtText = "????"
End Select

Thanks

 

[JEC]

Place that Old/New table somewhere in your sheet.
Then use this code to convert every old tekst to the new tekst

Sub jec()
  ar = Sheets(1).Cells(1, 12).CurrentRegion             'where old/new the table is
  With Application
      For Each cl In ActiveSheet.Cells(1, 1).CurrentRegion.Columns(1).Cells
        a = .Match(cl, .Index(ar, 0, 1), 0)
        If IsNumeric(a) Then cl.Value = .Index(ar, a, 2)
      Next
  End With
End Sub

 

[JenniferMurphy]

Place that Old/New table somewhere in your sheet.

That table is not in the sheet. I posted it for illustration purposes only. The actual sheet looks like this:

The headers will always be on Row 1. I want to scan Row 2 looking for any of the old terms. When I find one, as in G2, I'll then walk down Column G (G2, G3, G4, ...) replacing the old text with the new. Then do the same for the next column (H) if it also contains these terms.

Is that clearer?

 

[JEC]

Starts your Data in A1?
Whay if I just convert all old values, without looping through row 2 first?

Do you may have some sample data, so I can copy

 

[JenniferMurphy]

Starts your Data in A1?
Whay if I just convert all old values, without looping through row 2 first?

The sheet has about 700 rows and columns A-J at least. The data to be converted is in only 1 or 2 columns, which may or may not be adjacent.

I can select give each column a standard name. That might be the easiest.

 

[JEC]

You can try this then

Sub jec()
 ar = Sheets(1).Cells(1, 1).CurrentRegion
 jvold = Array("Mint", "Near Mint", "Very Good Plus", "Very Good", "Good Plus", "Good", "Fair", "Poor", "No Cover")
 jvnew = Array("1 Mint", "2 Mint-", "3 VGood+", "4 VGood", "5 Good+", "6 Good", "7 Fair", "8 Poor", "9 None")
 
 For i = 1 To UBound(ar, 2)
     If IsNumeric(Application.Match(ar(2, i), jvold, 0)) Then
        For j = 2 To UBound(ar)
           y = Application.Match(ar(j, i), jvold, 0)
           If IsNumeric(y) Then
              ar(j, i) = Application.Index(jvnew, y)
           End If
        Next
     End If
  Next
  
  Sheets(1).Cells(1, 1).CurrentRegion = ar
End Sub

 

[JenniferMurphy]

You can try this then

OK, I'll try it.

I usually run with Option Explicit. How should I declare those variables?

 

[JEC]

Dimension are not really necessary, but:

Dim ar, jvold, jvnew As Variant
Dim y, i, j As Long

 

[JenniferMurphy]

Dimension are not really necessary, but:

Dim ar, jvold, jvnew As Variant
Dim y, i, j As Long

I ran it without declarations and it seems to work. However, I have no idea how.

Any chance you could provide a bit of an explanation or add a few comments?

Thanks very much

 

[JEC]

This is what I can give you

Sub jec()
 Dim ar, jvold, jvnew As Variant
 Dim y, i, j As Long
 
 ar = Sheets(1).Cells(1, 1).CurrentRegion                                                                                   'load sheet data
 jvold = Array("Mint", "Near Mint", "Very Good Plus", "Very Good", "Good Plus", "Good", "Fair", "Poor", "No Cover")         'loading in old values
 jvnew = Array("1 Mint", "2 Mint-", "3 VGood+", "4 VGood", "5 Good+", "6 Good", "7 Fair", "8 Poor", "9 None")               'loading in new values, to compare/replace later
 
 For i = 1 To UBound(ar, 2)                                             'loop through row 2
     If IsNumeric(Application.Match(ar(2, i), jvold, 0)) Then           'if value in row 2 matches with an old value then move on and replace the values in that column
        For j = 2 To UBound(ar)                                         'loop through that specific column
           y = Application.Match(ar(j, i), jvold, 0)                    'looking for a match with old values (application.match returns a number)
           If IsNumeric(y) Then                                         'if a match is found/y is a number, then replace the old value with the new value (jvold to jvnew)
              ar(j, i) = Application.Index(jvnew, y)                    'where the replace takes place
           End If
        Next
     End If
  Next
  
  Sheets(1).Cells(1, 1).CurrentRegion = ar              'writing back the array (the code modifies the existing array which you loaded in)
End Sub