VBA Referencing Columns by Number

[mctopher]

I'm trying to figure out how to use VBA to reference a column by number instead of by letter.

The end goal of this portion of code is to insert a column for A, then in Column A flag each record with a "1" if it meets a series of conditions. The conditions are found in the "ConditionColumn" which will change location (which is why I'm trying to keep this variable instead of hard coding".

The line of code that I can't get to work is as follows:

    If Range(Columns(ConditionColumn), ReviewRowNumber) = 7 Then

Below is the full for this task.

Dim ReviewRowNumber As Long
Dim LastRowNumber As Long
Dim ConditionColumn As Double

    Sheets("MSS Data").Select
    LastRowNumber = Range("A1048576").End(xlUp).Row
    Columns(1).Insert
    ConditionColumn = WorksheetFunction.Match("Conditions", Sheets("MSS Data").Range("1:1"), 0)
    ReviewRowNumber = 2
    
While ReviewRowNumber <= LastRowNumber

    If Range(Columns(ConditionColumn), ReviewRowNumber) = 7 Then
    
    Range(A & ReviewRowNumber).Value = 1
    
    End If
    ReviewRowNumber = ReviewRowNumber + 1

Wend

If anyone could help me see what I'm doing wrong and help find a better way to accomplish this I'd be quite appreciative. I think it's something simple I'm just overlooking. Thanks in advance!

 

[hurgadion]

Hi,
I suggest the simple way. You can write

Range("A1")

as

Range(Cells(1, 1), Cells(1, 1))

Best regards.

 

[Scott Huish]

I haven't looked over the rest of your code, but use Cells instead of Range.

For cell C4, for example:

Cells(4, 3) = "A"

 

[Jonmo1]

Try

If Cells(ReviewRowNumber, ConditionColumn) = 7 Then

 

[mctopher]

Using "Cells" instead of "Range" did it. Thanks so much!