Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Need VBA to do the below calculations. The range however can be 100s of columns and 100s of row.

So, I need to be able to loop through.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

I found the below code; however, I don't know what to do to get it to loop through the ranges and apply the formula based on row and column.

Sub LoopRange()

Dim rCell As Range

Dim rRng As Range

ActiveSheet.Range("C16:C25").Select

For Each rCell In Selection

If Not IsError(rCell) Then rCell = rCell * "don't now what to do here"

Next rCell

End Sub

So, I need to be able to loop through.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A3) (B$1*B3) (C$1*C3) etc.

I found the below code; however, I don't know what to do to get it to loop through the ranges and apply the formula based on row and column.

Sub LoopRange()

Dim rCell As Range

Dim rRng As Range

ActiveSheet.Range("C16:C25

For Each rCell In Selection

If Not IsError(rCell) Then rCell = rCell * "don't now what to do here"

Next rCell

End Sub

(A$1*A3) (B$1*B3) (C$1*C3) etc.

(A$1*A4) (B$1*B4) (C$1*C4) etc.

(A$1*A5) (B$1*B5) (C$1*C5) etc.

(A$1*A6) (B$1*B6) (C$1*C6) etc.

(A$1*A7) (B$1*B7) (C$1*C7) etc.

(A$1*A8) (B$1*B8) (C$1*C8) etc.

(A$1*A9) (B$1*B9) (C$1*C9) etc.

(A$1*A10) (B$1*B10) (C$1*C10) etc.

(A$1*A11) (B$1*B11) (C$1*C11) etc.

```
activesheet.range("A16").formula="=A$1*A3"
activesheet.range("A16:A25").filldown
activesheet.range("A16:K25").fillright
```

It would be easy enough to convert them from formulas to values.

```
activesheet.range("A16:K25").value = activesheet.range("A16:K25").value
```

I've attached a worksheet where you can see original and what I eventually need to happen.

NOTE: The range of course would be much larger than this example.

I created a crude macro, but this would be very cumbersome if I had to do it that way.

Multiple-with-VBA-across-Range.xlsm

1. Select the first/top row of cells

2. Ctrl+C

3. Select all the cells below the top row

4. Paste Special (Values, Multiply)

Let me know if that gives you the values you want.

So the output is not correct in this solution.

the first row values are absolute valuesWhat does that mean?

Was your posted workbook a representative example of the data?

All values are multiplied by the first row. In the last solution, the past copy moves it down, and thereby the next row would multiply by the second row and so forth.

Range("C2").Value = Evaluate("C2*$C$1")

Range("D2").Value = Evaluate("D2*$D$1")

Range("E2").Value = Evaluate("E2*$E$1")

Range("F2").Value = Evaluate("F2*$F$1")

Range("G2").Value = Evaluate("G2*$G$1")

Range("H2").Value = Evaluate("H2*$H$1")

Range("C3").Value = Evaluate("C3*$C$1")

Range("D3").Value = Evaluate("D3*$D$1")

Range("E3").Value = Evaluate("E3*$E$1")

Range("F3").Value = Evaluate("F3*$F$1")

Range("G3").Value = Evaluate("G3*$G$1")

Range("H3").Value = Evaluate("H3*$H$1")

Thanks for your help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window