0

I am using excel 2016. I use Macros to automate the process since it is repetitive.

One step includes a situation where there are multiple cell ranges coming up. Since each time the cell references change i put a concatenate formula to add "average" and the range of cells together. Using concatenate formula it gives me a result like It will be like "=average(95:105)", or "=average(85:205)" depending on the cell reference it gives me the formula.

So i copy the formula automatically generated and paste it as values and run on my excel so it gives me the average of said to said cells.

I use macros to record the process. However the value in the cell that i used to record keeps on repeating when i try to run it with a different formula. Suppose I record "=average(a95:a105)" and the next time it runs i want to refer to the average of "=average(a105:a200)". But it keeps on pasting the formula =average(a95:a105) on a sheet where I want average(a105:a200). If you look at screenshot 5. In simple terms I want the value of cell B13 to run on cell B26 using macros. The value on cell B13 will differ each time according to the input i give.

Is there a way to automate macros to copy and paste different values in cells according to the values it has? Here is the macro.

Sub Macro9()

Macro9 Macro



Range("B13:B14").Select
Selection.Copy
Range("B26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=MIN(R[69]C:R[79]C)"
Range("B26:B27").Select
Range("B27").Activate
ActiveCell.FormulaR1C1 = "=MAX(R[68]C:R[78]C)"
Range("B26:B27").Select
Selection.AutoFill Destination:=Range("B26:N27"), Type:=xlFillDefault
Range("B26:N27").Select
End Sub

[Screenshot]11

Screenshot 2

SCREENSHOT 3

SCREENSHOT 4

screenshot 5

Pranav Sathyan
  • 1
  • 1
  • 2
    You forgot to share your code. You can [edit](https://stackoverflow.com/posts/68126400/edit) your post at any time. – VBasic2008 Jun 25 '21 at 06:33
  • 1
    It is difficult to understand what you need. Please, edit the question and post the code you use and better explain what you need against what the code returns. What does **I record "=average(a95:a105)"** mean? What **i want to refer to the average of** should mean? You must 'tell' to the code somehow what you want... Based on what the code should know wat you want? – FaneDuru Jun 25 '21 at 06:34
  • 1
    At a guess, since you don't give any example you could find the row number of the first relevant value, then the row number of the last relevant value then use indirect() to build the average function. – Solar Mike Jun 25 '21 at 07:11
  • 1
    Hi guys thanks for responding I have added the VBA code and a few screenshots of my explanation. Hope this will help. – Pranav Sathyan Jun 25 '21 at 08:43

0 Answers0