Thanks Otto - little more help please

[scaryboy]

Thanks Otto for your help. I really appreciate it.

The destination cells will be exactly 28 columns to the left of the
orginal cells.

I fooled around with some formats for the destination, but once again
i'm lost. Could you let me know what the format of the destination
will look like? I hope what i am asking is clear. The data will copy
and move along the row to a different column.

Thanks again.
Fred R.

 

[Ardus Petus]

i.Copy i.Offset(0,-28)

HTH
--
AP

Thanks Otto for your help. I really appreciate it.

The destination cells will be exactly 28 columns to the left of the
orginal cells.

I fooled around with some formats for the destination, but once again
i'm lost. Could you let me know what the format of the destination
will look like? I hope what i am asking is clear. The data will copy
and move along the row to a different column.

Thanks again.
Fred R.

 

[scaryboy]

Ardus Petus,

Thank you for your help. It still wont work correctly. The problem
isn't in the formula, but the fact that I didn't anticipate another
problem.

the copy cells are actually 9 merged cells in a 3x3 area. because they
are merged, when i manually select them and copy, then select the top
left corner of the paste area, it automatically copies over the new
3x3 area. The 3x3 target area is 9 cells each filled with different
data necessary until the paste occurs.

This is the entire macro as I have it:

Sub CopyCells()
Dim i As Range
For Each i In Selection
If Not IsEmpty(i.Value) Then
i.Copy i.Offset(0,-28)
End If
Next i
End Sub

it's actually working correctly except that the data is pasted into
one cell, instead of the merged 3x3 cell. The target 3x3 cell is
(0,-28) plus two cells to the right, by two cells down.

Is there anything I can do to make it paste into a bigger area? The
paste area is thus a range of cells, and not a single cell.

Thanks for your time.

Fred R.

 

[Ardus Petus]

Could you please mail me your Workbook, with expected results?

TIA

 

[scaryboy]

Ardus,
I just emailed the file to you. thanks for your time. lemme know what
you come up with.
TIA,
fred r.

 

[Ardus Petus]

Knowing the source range (in right box), I can calulate the destination 3x3
range, but how can I guess which one out of of the 9 dest cells should be
filled.

I noticed that in the right box, it is always the upper left cell that holds
the value.

 

[scaryboy]

Ardus,

thanks again.

i need the same cooresponding cells to be filled. The set of boxes on
the right, will look identical to the boxes on the left when the
puzzle is finished. But, it's got to fill the entire 3x3 set of boxes,
not just one of the 9. I manually cut and pasted a few to show what i
need it to look like.

Is that clearer? I hope I understand what you are asking.

thank you,
fred r.

 

[Ardus Petus]

Here is your code.
I added checking the selection position.

HTH
--
AP

'-------------------------------------------------------
Sub CopyCells()
If Intersect( _
Selection, _
Range("AC3:BC29") _
) Is Nothing Then
Beep
Exit Sub
End If
With Selection.Offset(0, -28).Resize(3, 3)
.ClearContents
.Merge
.Font.Size = 14
.Cells(1, 1).Value = ActiveCell.Value
End With
End Sub
'------------------------------------------------------

 

[scaryboy]

Ardus
works great for bringing over one number at a time, merging the cell
and copying the data. So much better than doing it manually each and
every cell.

thanks for your help.


I've got to find a way to make it run the entire matrix, or a
selection, skipping cells that are blank (right now it will destroy
the data in the left matrix if i select a blank in the right). Also,
if i select two cells, it will only copy and paste the first, then
stop. But, it's so much better than what i had. I'm grateful for your
help.

thanks again,
fred r.

Here is your code.
I added checking the selection position.

HTH

Sub CopyCells()
If Intersect( _
Selection, _
Range("AC3:BC29") _
) Is Nothing Then
Beep
Exit Sub
End If
With Selection.Offset(0, -28).Resize(3, 3)
.ClearContents
.Merge
.Font.Size = 14
.Cells(1, 1).Value = ActiveCell.Value
End With
End Sub