0

I'm trying to divide a set number of columns (A to BL) in sheet1, with the same number of columns in sheet2. Each cell in the first sheets range should be divided by the same cell in the second sheet.

The numbers of rows however are dynamic. (the same between sheets, but number per sheet will change weekly)

Im also trying to paste the answers into sheet 3.

I've been trying to build off of recorded macros but am not getting anywhere.

Sub Macro5()

    Range("A1").Select
    ActiveCell.Formula2R1C1 = "=Result2!R[1]C[1]:R[6]C[4]/Result!R[1]C[1]:R[6]C[4]"
    Range("E15").Select

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
doug mackie
  • 29
  • 5
  • Have you considered (1) copying the numerator set into sheet 3, (2) copying the denominator set onto the *clipboard*, and (3) Doing a _Paste Special..._ -> `Divide` onto Sheet 3? This seems like perhaps the cleanest way, and it's scriptable in VBA. – Marc Feb 28 '22 at 20:45

1 Answers1

0

This macro is auto-generated, using the steps I outlined in my comment above. It only copies one row for columns A thru C, but you said your set will be variable, so you can customize as you see fit.

Sub DivideSheet1ValuesBySheet2Values()
    'Copy numerators and paste them into a new Sheet3
    Range("A1:C1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste

    'return to Sheet2 and copy the denominators to clipboard
    Sheets("Sheet2").Select
    Range("A1:C1").Select
    Application.CutCopyMode = False
    Selection.Copy

    'Activate Sheet3 and Paste Special - Divide
    Sheets("Sheet3").Select
    Range("A1:C1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks _
        :=False, Transpose:=False
End Sub

Procedure

  1. Copy the numerator set from Sheet1 to Sheet3
  2. Copy the denominator set (Sheet2 values) to the clipboard
  3. Do a Paste Special... -> Divide, which dumps the clipboard values onto the values in Sheet3 while applying a division calculation.
Marc
  • 11,403
  • 2
  • 35
  • 45
  • 1
    Just a side note: generally answers in the VBA tag space [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Feb 28 '22 at 20:52
  • @Marc Thanks, this procedure is easy to follow and build off of. Regarding the number of rows it copies, how would I set it to copy rows until last row/blank space? – doug mackie Feb 28 '22 at 21:03
  • @dougmackie, Stackoverflow has answered that question many times. E.g. here's a popular one for determining last row: https://stackoverflow.com/a/11169920/1024832. – Marc Feb 28 '22 at 21:09
  • @BigBen, thanks. I was just applying the auto-generated code, but will consider that going forward. – Marc Feb 28 '22 at 21:11