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, Commented Dec 10, 2015 at 14:35
@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
– Scott Holtzman, Commented Dec 10, 2015 at 14:38
Thanks @ScottHoltzman I noticed that, just tried to bring that to OP's attention, sometimes refusal to think is overwhelming... — EEM
– EEM, Commented Dec 10, 2015 at 14:42
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
– Rosetta, Commented Dec 10, 2015 at 15:31

Scott Holtzman · Accepted Answer · 2015-12-10 14:43:01Z

1

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 :)

edited Dec 10, 2015 at 14:43

answered Dec 10, 2015 at 14:36

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Poyraz Özer Over a year ago

Sorry it doesn't work me. Actually maybe my explanation is wrong or missing. I had sometimes 10 rows sometimes 15 rows in my column A. And I want to write length of each A column cell to B1 cell. And that's why firstly I wrote how many rows I had in column A with COUNT formula to use this value as my range end. By the way, formula didn't work or I couldn't progress it.

Scott Holtzman Over a year ago

so you want to return 10 or 15 (or whatever the number is) Length values into one cell (B1)? Or can you provide a screenshot with your data and what you want your end result to be?

Poyraz Özer Over a year ago

No no. It's just an example. It could be 100, or 1.000. I mean whatever the number is.

Scott Holtzman Over a year ago

@PoyrazÖzer - you still didn't answer my question. Do you want to return the length of each cell in column A in the corresponding row in column B? If yes, then why doesn't the code above not work for you? If not, please update your OP with a screenshot of your data and expected result.

Poyraz Özer Over a year ago

Yes, B1=LEN(A1), B2=LEN(A2), B3=LEN(A3)... and goes on. I don't know why it didn't work.

|

Collectives™ on Stack Overflow

Excel use cell value as range in VBA

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related