VBA Referencing Columns by Number

mctopher

Board Regular
Joined
Jun 23, 2011
Messages
192
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:

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

Below is the full for this task.

HTML:
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi,
I suggest the simple way. You can write
Code:
Range("A1")
as
Code:
Range(Cells(1, 1), Cells(1, 1))
Best regards.
 
Upvote 0
I haven't looked over the rest of your code, but use Cells instead of Range.

For cell C4, for example:
Code:
Cells(4, 3) = "A"
 
Upvote 0

Forum statistics

Threads
1,211,960
Messages
6,105,081
Members
447,947
Latest member
OX_2005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top