Show value of cell above

[SHAY]

Hello friends,

I need your help with a code to copy the cell value of the cell above in specific cells (EVERY OTHER COLUMN):

my worksheet:

14422

need to copy:
D4=D3 , D6=D5, D8=D7 etc...
E dont copy!
F4=F3 , F6=F5, F8=F7 etc...



Any suggestions?
Tnx

[p45cal]

Try the following which needs you to select a range on the sheet before running it, the trick being to select the right area (the top left cell of your initial selection will be one of the cells that gets filled); in the case of your picture that would be cells D4:H10.
In any event, it selects the cells it proposes to fill in before asking for your confirmation:
Sub blah()
'Range("D4:H10").Select 'testing line.
Set RangeToProcess = Selection
Set myColms = RangeToProcess.Columns(1)
For colm = 3 To RangeToProcess.Columns.Count Step 2
Set myColms = Union(myColms, RangeToProcess.Columns(colm))
Next colm
Set MyRws = RangeToProcess.Rows(1)
For Rw = 3 To RangeToProcess.Rows.Count Step 2
Set MyRws = Union(MyRws, RangeToProcess.Rows(Rw))
Next Rw
Set myrng = Intersect(myColms, MyRws)
myrng.Select
myAns = MsgBox("Confirm the selected cells are those you want to assume the value of the cell above", 4, "Continue?")
If myAns = vbYes Then
For Each cll In myrng.Cells
cll.Value = cll.Offset(-1).Value
Next cll
End If
End Sub

[SamT]

Less Obvious, but shorter

Sub SamT()
'Select Range to process, starting with the upper left empty cell to process
Dim C As Long
Dim R As Long

With Selection
For C = 1 To .Columns.Count Step 2
For R = 1 To .Rows.Count Step 2
.Cells(R, C).Value = .Cells(R - 1, C).Value
Next
Next
End With
End Sub

[SHAY]

Thanks Gurus!

Both work great!
The thing is i need it to copy all the values at once.
(with out a msg box is better)
* if it mathers, I use it on a protected sheet. only the beige cells are Unlocked)

[p45cal]

The thing is i need it to copy all the values at once.
Then SamT's your man… if the fraction of a second it takes his macro to run counts as all at once.

[SamT]

Sub SamT()
'Select Range to process, starting with the upper left empty cell to process
Dim C As Long
Dim R As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

With Selection
For C = 1 To .Columns.Count Step 2
For R = 1 To .Rows.Count Step 2
.Cells(R, C).Value = .Cells(R - 1, C).Value
Next
Next
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.Calculate
End With
End Sub

[SHAY]

THANKS!

tried the new one as well but it only copies
the value of the selected cell.
This is how i add the Range, maybe I did it wrong:

--------------------------------
Sub SamT()

'Range("D7:N71")


Dim C As Long
Dim R As Long

Application.ScreenUpdating = False
Application.EnableEvents = False
etc...
----------------------------------------------------

[p45cal]

Again (but it applies to both codes):
needs you to select a range on the sheet before running it, the trick being to select the right area (the top left cell of your initial selection will be one of the cells that gets filled); in the case of your picture that would be cells D4:H10.So that's what you select before running the macro, not just a single cell.

[SamT]

Instead of "With Selection", use "With Range("D7:N71")"

When you said "all at once," I thought it was because some calculation might change the values of some cells before they were "copied." I merely prevented that.

If you truly, for some non-Excel reason, (Like a school assignment,) need all the cells values copied all at once, then p45cal is the man. Just duplicate his process of setting myrng for two ranges, myrng1 =cells to copy and myrng2 = cells to paste into, then

myrng2.Value = myrng1.Value.

[SHAY]

It works great using With Range("D7:N71")
Like you suggested - all cell values are copied at once!

But when i protect the sheet again
I get an error once it gets to the first protected cell.

(in yellow: .Cells(R, C).Value = .Cells(R - 1, C).Value)

14427

(only the beige cells are Unlocked)

Is it possible to add a code to ignore/skip to next if cell is locked?

[p45cal]

SamT,

myrng2.Value = myrng1.Value.
This copies the value of only the first area of myrng1 to all the cells of myrng2.


I had earlier experimernted with:

With myrng
.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End Withbut had a similar problem.



SHAY,

But when i protect the sheet again
I get an error once it gets to the first protected cell.

Is it possible to add a code to ignore/skip to next if cell is locked?
Yes, in my code change:
cll.Value = cll.Offset(-1).Valueto:
If Not cll.Locked Then cll.Value = cll.Offset(-1).Value



In SamT's code change:
.Cells(R, C).Value = .Cells(R - 1, C).Valueto:
If Not .Cells(R, C).Locked Then .Cells(R, C).Value = .Cells(R - 1, C).ValueBoth these changes will prevent a locked cell from receiving a new value whether or not the sheet is protected (we can change this behaviour if necessary).

If you wanted the locked cells to be overwritten anyway you could either:
unprotect the sheet in the code first, write to the locked cells, then protect the sheet in code again
or:
use a form of Protect in the code which allows code to change the cells but not users (UserinterfaceOnly:=True)… come back if that's the case.

[SHAY]

Thanks you so much p45cal and SamT!

you guys are the best.

works like a charm!
:congaline

[p45cal]

This is weird.
This:
14428

is the result of:
Sub zzz()
Set myrng1 = Range("B2:C2")
Set myrng2 = Range("D4,F4:F5,H4:I4,K4:L5,N4:P4,N6:N8,J7:L9,E7:H7,E9:E12,G10:H12,G14:J17")
Set myrng3 = Range("B20:B21")
Set myrng4 = myrng2.Offset(16)

myrng2.Value = myrng1.Value
myrng4.Value = myrng3.Value
'myrng4.Value = myrng2.Value
End Sub

The least expected were:
K4:L5 and E7:H7 at the top,
F20:F21 and K20:L21 below.

If you enable the last line, all the cells in myrng4 become hi1

[SamT]

Possible related to the way Excel numbers cells; From left to right then from top down




Range 1





1
2
3
4





Range 2


1


2


3


4





Range 3



1
2


3
4