Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copying current cell's value to another cell

  Asked By: Armando    Date: Dec 30    Category: MS Office    Views: 1747
  

I hope I can explain this clearly. If the current cell is one of the
following, I would like to copy its value to another cell. Say the current
cell is one of the following:
A4 - G4
A6 - G6
A8 - G8
A10- G10
A12 - G12
A14 - G14

All right, now lets say I am currently on Cell B12, which at this time has a
value of 26. I would like to copy that value to M7. Any time the current
cell changes so long as it is one of those I mentioned above, the value of
that cell is copied to M7, where I will do more calculations on it.

I tried a few functions, but they work one time, and moving around does not
change the M7 accordingly. It is probably going to require VBA, but can it
be done without VBA? IF it has to be VBA, could some kind soul help me out
please?

If the current cell is outside those ranges, just a blank would be very
sufficient.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Aakifah Khan     Answered On: Dec 30

You say "Any time  the current  cell changes ...". Do you mean that the
contents of the current cell  changes, or do you mean that you move the
selection to a different cell?

I'll assume that you are talking about changing the selection.

If so, you need code - not formulas. You will need to use the "worksheet
selection change" event. That event has a parameter which is the cell that
is the current selection.

Best way is to use the Intersect function to see if there is an intersection
between the current cell and a Range function call containing a
comma-separated list of your various small ranges.

If there is an intersection, then you know it's one of your wanted cells.
Simply assign the value of the active cell into the value property of M7.
Otherwise, assign an empty string.

A few Google searches will get you appropriate examples.

Note that this will only work  when the selection changes. I.e. it will not
work for the initial position of the active cell.

 
Answer #2    Answered By: Chelsea Rose     Answered On: Dec 30

You say "Any time  the current  cell changes ...". Do you mean that the
contents of the current cell  changes, or do you mean that you move the
selection to a different cell?
-End-

Both. The contents of the cell will change, and I (or whoever is using the
program) will move the selection to a different cell. Googling brought me
odd suggestions, the most common suggestion I hit was a way to report the
row the selection is on, not the value of the selection itself. It is for
dates on Cemetery headstone work, meaning the range of values will be from 1
to 31.

 
Answer #3    Answered By: Leigh Carpenter     Answered On: Dec 30

Reporting the row means that you would use the Row property of the
selection. To report the value, use the Value property instead.

However, if you are wanting to also pick up changes to the cells  themselves,
you will need to do either of two things ...

1) Handle the worksheet's "Change" event as well. That event will get
fired when the contents of a cell  are changed. Or

2) Instead of putting the value of the source cell into M7, you can put a
formula that links M7 to the active cell. You would do this by assigning
the formula (which is simply a string) to the Formula property of M7. You
can get the address of the source cell (to put in the formula) by looking at
its Address property.

 
Didn't find what you were looking for? Find more on Copying current cell's value to another cell Or get search suggestion and latest updates.




Tagged: