Excel use cell value as range in VBA

Question

I have an excel file. There is a changable row quantity for column A for every time and that's why I need to make dynamic formula. For example;

I want to write "=LEN(B1)" formula on B1. And when make double click on right down corner of the cell, it's going to the end of until when column A values ends. That's why, before all this process, I wrote "=COUNT(A:A)" on cell C1.

In my VBA, I want to write;

Dim TEMP As String
TEMP = Range("C1").Value
Range("B1").Select
ActiveCell.FormulaR1C1="=+LEN(RC[-1])"
Selection.AutoFill Destination:=Range("B1:TEMP")

But there is something wrong in this process. As a result, I just want to use a cell value in my VBA as a range or string or number.

Thanks for your support.

=LEN(B1) formula on B1 gives you a circular reference! — EEM
@EEM - His code and his explanation say two different things. If you look at the code, it makes more sense ... OP seems to want to write =Len(A1) on cell B1. — Scott Holtzman
Thanks @ScottHoltzman I noticed that, just tried to bring that to OP's attention, sometimes refusal to think is overwhelming... — EEM
Hello @PoyrazÖzer, is it you want to write VBA code that can fill down the formula in B1 over column B depending on how many rows there is in column A that have data? is this your objective of writing the VBA code ? — Rosetta

Scott Holtzman Scott Holtzman · Accepted Answer · 2015-12-10T14:36:57

Two notes:

It's best practice to always qualify your objects (workbooks, worksheets, ranges, etc.) beforehand
When you use R1C1 notation, you can just write the formula directly to the range (with no need for AutoFill or FillDown)

Try this:

Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") 'edit for your sheet name
Dim lRow as Long
lRow = ws.Range("A" & ws.Rows.count).End(xlup).Row
ws.Range("B1:B" & lRow).FormulaR1C1 = "=Len(RC[-1])"

And just as a side note worth mentioning the way you wrote Range("B1:TEMP") is not proper syntax. Correct syntax would be Range("B1:B" & TEMP), which, of course, would only work if TEMP was indeed a numerical value :)

Excel use cell value as range in VBA

1 Answers