Pasting clipboard value in excel macro formula

Question

I am trying to create excel macro that changes a value in a formula with clipboard value.

I want to have relative addressing for some part of the formula and absolute addressing for remaining part of formula. Absolute address value I want to paste from clipboard.

For example in the below code,

ActiveCell.FormulaR1C1 = "=RC[-11]*(RC[-2]+R[-34]C[-2])"

In place of R[-34]C[-2] I want to give R[-75]C[-2] and I have this 75 in clipboard.

So, whenever I execute the macro 2nd part of formula should take an absolute address from clipboard.

My current code is as follows:

Sub Macro1()
    ActiveCell.FormulaR1C1 = "=RC[-11]*(RC[-2]+R[-34]C[-2])"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-16]=1,RC[-18]-R[-34]C[-18],1)"
    ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

Appreciate any help in this regard.

Is there some specific reason you're using the clipboard here? Where does the "75" come from originally? — Tim Williams, Oct 11 '21 at 17:32
This 75 comes from some other file which I cannot parse in this script. so, i take it in clipboard and want to use in my current formula. — sridhar, Oct 12 '21 at 08:16

score 1 · Answer 1 · answered Oct 11 '21 at 11:17

... and why would you want to do that? Using the clipboard is a bad thing to do while creating VBA macros, hence this post, explaining how to avoid this.

And you would like to use it in a formula?

No no, that's a bad idea.

As far as formulas and macros are concerned, you can do the following:
Replace:

ActiveCell.Formula = "=A2 * A3";

By:

ActiveCell.Formula = "=" & ActiveCell.Offset(1,0) & " * " & ActiveCell.Offset(1,1)

... but don't put clipboard references in formulas.

Pasting clipboard value in excel macro formula

1 Answers1