CODEWITHSUNDEEP
vbaexcel
Poyraz Özer
Poyraz Özer

Reputation: 5

Excel use cell value as range in VBA

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.

Upvotes: 0

Views: 1557

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

Two notes:

  1. It's best practice to always qualify your objects (workbooks, worksheets, ranges, etc.) beforehand
  2. 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 :)

Upvotes: 1

Related Questions