CODEWITHSUNDEEP
excelvba
user1302187
user1302187

Reputation: 13

Excel 2010 VBA cell math

I'm looking to assign either a value to a range that would essentially be equivalent to me typing
"=A1*2" in cell B1 in the main sheet window and then dragging that function down 10 times (so B2=A2*2
B3=A3*2
,...,
Bn=An*2

With ThisWorkbook.Worksheets(sheetname)
  Dim temp As Range, temp2 As Range
  Set temp = Range(Cells(1, 1), Cells(10, 1))
  Set temp2 = Range(Cells(1, 2), Cells(10, 2))
  temp2.Value = temp.Value * 2
End With

An error is thrown when I try to assign the value to the temp variable. I have a suspicion that my method is invalid, but I can't seem to fix it.

Thanks!

Upvotes: 1

Views: 2639

Answers (2)

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

This is how I would rewrite your code, assuming you want values (which is what you specify in the question) and not formulas:

Dim temp As Variant
Dim nRows As Long
Dim iRow As Long
Dim sheetname As String

sheetname = "Sheet1" 'or whatever
nRows = 10

With ThisWorkbook.Worksheets(sheetname)
    temp = .Range(.Cells(1, 1), .Cells(nRows, 1)) 'Load range to a variant array
    For iRow = 1 To nRows 'Loop through array to multiply elements by 2
        temp(iRow, 1) = temp(iRow, 1) * 2
    Next iRow
    .Range(.Cells(1, 2), .Cells(nRows, 2)) = temp 'Write array back to range
End With

Note that you have to say .Range and .Cells with the . dot. If you don't, you're not qualifying the properties correctly; they won't refer to ThisWorkbook.Worksheets(sheetname) but to whatever sheet happens to be active at the moment.

The second point is that you can't multiply an entire array at once (or an entire range, for that matter). You have to loop through the elements and multiply them individually. I choose to load the range to an array, and loop through that, because doing this is orders of magnitude faster than looping through cells of a range. This may not matter much if you only have 10 cells, but it certainly will if you have many more.

Upvotes: 1

brettdj
brettdj

Reputation: 55692

In one line just

ThisWorkbook.Worksheets(sheetname).Range("B1:B10").FormulaR1C1 = "=RC[-1]*2"

Upvotes: 3

Related Questions