Using a Variable in Range Property

[Gard5096]

I am trying to place a variable into the range property, but it isn't working like I thought it would. The ???? represents where I want to place variable i9. i9 is defined as an entire column.

Your help is much appreciated.

Please Login or Register  to view this content.

[Norie]

Is i9 the name of the variable or a cell reference?

[Gard5096]

i9 is a variable for a column

[Norie]

Do you mean it's a column letter?

If it is.

Please Login or Register  to view this content.

[Gard5096]

Here's what the variables are defined as:

Dim i, i8, i9 As Long
Dim rIndex As Long

i = Application.WorksheetFunction.Match("V91", Range("1:1"), 0)
i8 = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
i9 = Cells(1, i8).Offset(, 1).Column

The .Range(i9 & lLastRow).FillDown is the specific part that I can't get to work.

Please Login or Register  to view this content.

[Norie]

It's a column number, so you need to use Cells instead of Range.

Please Login or Register  to view this content.

[Gard5096]

Ok so it's no longer giving me an error, but it isn't filling the formula down.

Here's where I'm at:

Please Login or Register  to view this content.

[Norie]

What's the formula you are trying to fill down?

[Gard5096]

Cell(2, i9) will be populated with ".Formula = .Cells(2, i) <> "" And .Cells(2, i) >= .Cells(2, i8)
" which translates into =AND(i<>"", i>=i8)

I'm trying to get this to fill down to every row. I think the problem is that the formula isnt actually visible on the worksheet, just the value resulting from the previous formula: TRUE or FALSE

[Gard5096]

I really have no clue why this isn't working. The formula works and places a "FALSE" in .cells(2, i9), but it will not fill the same formula down to the rest of the rows in column i9. Is it because .cells(2, i9) doesn't display a formula in the worksheet? It only displays the result of the formula from the macro, which is "FALSE"....but if that was the case, it would at least fill down the value "FALSE" to all rows. I'M SO CONFUSED AS TO WHY THIS ISN'T WORKING.

Please help me out if you can.

Thank you.

Please Login or Register  to view this content.

[Norie]

You aren't putting a formula in a cell, you are putting the results of an expression - False.

If you copy that same expression down it will be unchanged so you'll end up with the same value, False, in each cells.

[Gard5096]

Norie,

I really appreciate your input, but my problem is still unsolved. Do you know how to make the expression populate to the rest of the column in the active data set? The data set is very large, so I need a way of doing it without using a loop.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Gard5096]

TMShucks,

Thank you for your response. I see what you did to the With statement, which is an improvement.
My problem still lies in using variables in the formula. Here's what it will look like, which won't work:

Please Login or Register  to view this content.

Are there any work arounds?

[TMS]

Maybe something like:

Please Login or Register  to view this content.

Regards, TMS

[Gard5096]

Hmmm, it looks like that is referencing the column number, rather than each row in the column. This is what the result was

Please Login or Register  to view this content.

There has to be some way to mention the row in the formula, but I'm not sure how. Or maybe it's the variable that needs to be changed. Thoughts?

[Gard5096]

Is there a possibility that it can only be done with a loop?

[Norie]

What is the formula?

[Gard5096]

The VBA formula is:

Please Login or Register  to view this content.

And altogether, here is what the macro looks like:

Please Login or Register  to view this content.

The result of this macro is the formula, "=AND(89<>"",89>=110)" in every row for column i9Variable. What it is doing is grabbing the column numbers of the variables and placing them in the equation. For example: iVariable is in column "CK" which is the 89th column, and i8Variable is in column "DF" which is the 110th column. The reason the equation uses the exact same numbers in every cell for column i9Variable, is because the formula only references the columns of iVariable/i8Variable, and not each corresponding
row. This is where my trouble is. What I need to know is how to include each row reference in the VBA formula.

[Norie]

No, what is the actual worksheet formula you want to put in a cell then fill down?

If all you need to change in the formula is the row references Excel will do it for you.

[Gard5096]

The actual formula would be:

Please Login or Register  to view this content.

The problem is that I cannot use the column indexes (CK, DF, DG) because columns may be added to the report, causing the columns to shift and changing the data in CK. I assigned these columns to a variable, and I'd like to use the variables in the formula.

[Norie]

What you need to do is use the Address property.

Please Login or Register  to view this content.

[Gard5096]

Norie,

That was the solution I needed. Thank you for your expertise and follow-ups. You have been a great help!