Adjust code to add variable for column letter

[ChristineJ]

The following is part of code for a macro I run on different rows in column P. Example: Grading_P(10), Grading_P(22), etc.

I now need to use it on rows in different columns, not just column P. Can this be tweaked so the column letter is also a variable to replace the "P" in the code below?
Example: Grading_P(E15), Grading_P(G21), etc.

I'll still need to use only the row number in specific columns, such as in the last two lines of the code below (columns CD and BZ). Thanks!

Sub Grading_P(rowNum As Integer)

Dim rowNumber As Integer

rowNumber = rowNum

Range("P" & rowNumber).ClearComments   

Range("P" & rowNumber).Offset(0, 1).Value = "  " & "X "        'start by setting to X

If IsError(Range("P" & rowNumber)) Then
Range("CD" & rowNumber).Value = "This formula results in an error and cannot be evaluated."
Range("BZ" & rowNumber).Value = 0

 

[Joe4]

Do you mean something like this?

Sub Grading_P(rowNum As Integer, colLet As Integer)

Range(colLet & rowNum).ClearComments   

Range(colLet & rowNum).Offset(0, 1).Value = "  " & "X "        'start by setting to X

By the way, there is no reason for the "rowNumber" variable. You can use the "rowNum" parameter variable directly in your code, you do not need to set it equal to another variable. That is an unnecessary extra step.

Also note that you can use Cells(row, column) to refer to ranges instead of Range. The advantage to that is that you can use either a letter or number reference for the column argument.
So:

Range("P10")

is the same as:

Cells(10, "P")

is the same as:

Cells(10, 16)

(since "P" is the 16th column)

Using a numeric reference for columns comes in real handy when you are trying to loop through columns, especially as you get past column Z.
It is much easier to use numbers in that case.

 

[ChristineJ]

Thanks - the code you provided is what I'm looking for. I understand what you mean by the redundancy of the rowNum and rowNumber variables. (I had lifted that code from somewhere else and tweaked it, and that is what was there.) I also get what you're saying about Cells(Row, Column). I really appreciate these explanations.

I'm still not quite there. In my original code I would call the macro by inserting the row number in the parenthesis, such as Grading_P(10).

With the additional variable colLet, what do I insert within the parenthesis when I call the macro? The goal would be Grading_P(P10), Grading_P(E15), etc. I've tried these, with and without commas, ampersands, quotes, and no success.

Thanks for this help! I am very new at VBA variables. C

 

[Joe4]

The way I wrote the code, we ask for the row number and column letter separately.
However, I did make one mistake - we want the column letter to be a letter (not a number), so we need to declare that as a string, i.e.

Sub Grading_P(rowNum As Integer, colLet As String)

So whenever calling that sub procedure, you just put those parameters in the exact same order as shown in the parameter declaration, i.e.

Grading_P(10, "P")

 

[ChristineJ]

I did try changing colLet As Integer to colLet As String, so I'm glad I was on the right track!

I was also trying to use Cells rather than Range, and that is where I must have gone wrong. I'm back to Range, and it is working perfectly now!

Thanks for all this help and for sticking with me on this! C

 

[Joe4]

You are welcome!
Glad I was able to help!

If you wanted to use Cells. it would look something like:

Cells(rowNum, colLet).ClearComments