Macro and formula

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I need help to be able to create a macro which for example will use the following formula:
=sum(d1+f1 -1), the result is in g1, now I want to be able to use this to work on different corresponding rows with the results in column g, taking digfferent values from respective rows corresponding to d and f and g.
drag is possible at times, but as a new spread sheet is used for data the formula has to be recreated or by opening another spread sheet a copy paste carried out.
but time consuming.
now currently by recording the macro as per the above formula the result and direction only go to g1.
so if i choose row 100 and apply the macro in g100 than of any numeric data in row d100 and f100 is not produced as a result in g100 but only what is in fi and d1 -1 is produced.
As I am not much in knowledge of the codes and how to go about with the complexes your contribution will help
Regards

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not too sure what you are trying to do and even why. Any how it seem thats you what you need to know is what row you are on before you run the macro

So first locate the row on which you are.

Selection.Row should give you the first row select
Selection.Rows.Count will give you the number of rows that you have select

in you formula you can have some thing like


Cells(myRow, "G").formula ="=Sum(D" & myRow & " + F" & myRow & " - " & myRow & ")"

Myrow is the row that was selected and you have already found out