asked on

Active cell to equal another cell

Hi

I am running a macro that splits data and add rows etc. What I would like to do now is get one cell to equal another. I want the user to click on a cell (so this will be the active cell) and the macro goes to the bottom row and the active cell will equal this. I hope this makes sense. Please let me know if I can explain anything further.

TIA

Subodh Tiwari (Neeraj)

As per your description, you only need one line of code...

Sub ChangeValue()
ActiveCell.Value = ActiveCell.Offset(1)
End Sub

Open in new window

ajaeclarke

ASKER

Hi Subodh

Thanks for you reply. I couldn't get this to work.

I have uploaded an example of what I need. Hope this will help explain it a bit.

Thank you :)
Example.xlsx

Subodh Tiwari (Neeraj)

You mean if you select D2 and run the macro, D2 should be equal to the D11. Right?

If your answer is yes, give me the logic behind this. Why D11 and why not some other cell in column D?

Also, other than selecting D2 what other cells you may select before running the macro and what output in that case do you expect. Give me at least one more example with the logic.

The color you have in some of the rows on the sheet is for visualization purpose or it has some meaning to your data?

ajaeclarke

ASKER

I mean D2 should =D11 so when the user changes the data in D11 the change will also happen in D2. This forms a part of a much bigger process that is driven by the user. The color is purely for visualization.

Roy Cox

maybe

ActiveCell.Value = ActiveCell.End(xlDown).Value

Open in new window

Subodh Tiwari (Neeraj)

I don't think you have explained it well.

My question was why D2 should be equal to the D11? Why not D12 or D13? Is the data in row2 is related to the row11 in some way?

How would the code decide that if D2 is the selected cell and the macro is run, it should place the value of D11 in D2? Any logic?

You said...

when the user changes the data in D11 the change will also happen in D2

It appears that you need a Change Event Code which should run automatically if some cells are changed and due to this some other related cells must also be changed at the same time by the code.

Roy Cox

I've just looked at the example workbook and it's very poorly designed. Why can't you have the data correctly entered in a table format?

ajaeclarke

ASKER

Hi Roy. Thanks for your feedback. It actually is only an example and is nothing like the actual workbook but I can’t use that as an example as it contains confidential information. As Subodh says, I am obviously not explaing it well so I will have a think and try to reword it. 🙄

Roy Cox

The example needs to represent the actual workbook. Just overwrite confidential data (names, etc) with nonsense.

It's also, a very bad idea in coding to rely on using ActiveCell, what if the user accidentally selects the wrong cell?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
       Experts Exchange Top Expert VBA 2018
       Experts Exchange Distinguished Expert in Excel 2018

ajaeclarke

ASKER

Thanks Martin. I will check out your articles :)