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

 

[JenniferMurphy]

This is what I can give you

Perfect. Thank you very much. It will take me a little time to figure it all out. Excellent work.

 

[JEC]

You're welcome! Good luck

 

[Peter_SSs]

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

Good idea. It helps avoid (sometimes unnoticeable) errors if you do spell a variable correctly through the code. It also allocates memory more efficiently.

Dimension are not really necessary, but:

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

If declaring the first 3 variables as Variant and the last 3 as Long, the second line needs to be as below, else y and i will be allocated as Variant.

Dim y As Long, i As Long, j As Long

I would recommend the same explicit declaration of the Variants for consistency

Dim ar As Variant, jvold As Variant, jvnew As Variant

 

[dmt32]

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

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

Hi,

Just be aware that VBA does not let you declare a group of variables on one line for a particular data type just by separating the variables with commas.

Declaring several variables on the same line with single data type at the end is a common mistake

Dim ar, jvold, jvnew As Variant

These will all be variants type as VBA uses the default type Variant for any variable with an undeclared data type.

However, these

Dim y, i, j As Long

In this latter case, only j is being declared as Long data type. For anything other than a Variant, each variable needs to be declared explicitly with its data type

Dim y as Long, i As Long, j As Long

Personally, I would suggest declaring variables with their data types even for a small project is a good habit to get in to & having Option Explicit declared should ensure that you don’t forget.

Hope Helpful

Dave

 

[JEC]

I stopped using declarations long time ago, forgot the rules a bit?

 

[dmt32]

I stopped using declarations long time ago, forgot the rules a bit?

VBA does allow you to do just that but like when you were taught to drive a car (mirror, signal, manoeuvre) after passing the test you find you don’t always do it but not doing so comes with risk & it is the same with VBA, forgetting to do it can result in your application not behaving as intended giving unexpected results also, it can make complex code difficult to follow.

Personally, when I respond to assist an OP, I endeavour to ensure that I include variable declarations & their data types even if it makes my suggestion look a little longer.

Dave

 

[JEC]

But if you taught to drive well enough to not use the help of an instructor?…just joking. Whatever you like best. I think that is different voor everyone.

 

[JenniferMurphy]

Oooppss. Somehow, I missed most of the replies. Sorry.