Paste formula keeping the source formula theme

[auhom_me]

I want to input the value of B2 in cell F2,F3,F4 , value of B3 in F5,F6,F7 and so on like the followings. I tried to put the value B2 by referencing in F2,F3,F4 manually then copy all 3 cell(F2,F3,F4) and paste special (all using source theme ) immediate next 3 cell but it didn’t work rather giving the value of B5(WEDNSDAY in F5,F6,F7 instead of B3(MONDAY) value. Please help me. I am Using 2010 but I have to apply it in office 2007. Formula is preferable to vba

	A1	B1		E1	F1
A1	DAY 1	DAY 2		1	SUNDAY
A2	1	SUNDAY		1	SUNDAY
A3	2	MONDAY		1	SUNDAY
A4	3	TUESDAY		2	MONDAY
A5	4	WEDNESDAY	2	MONDAY
A6	8	THURSDAY	2	MONDAY
				3	TUESDAY
				3	TUESDAY
				3	TUESDAY
				4	WEDNESDAY
				4	WEDNESDAY
				4	WEDNESDAY

<tbody>
</tbody>

 

[BrianJN1]

Well that was easier than I expected.

In these cells put the formula assigned:
E2 =A2, E3 =E2 and E4 =E3
Now in E5 enter this formula: =IF(MOD(E2+E3+E4,3)=0,E4+1,E4)
Copy it down as far as your need.

Now go to F2 and enter this formula:
=VLOOKUP(E2,A$2:B$6,2,FALSE)

Copy it down as far as you need.

One small warning here, I have set the range in the VLOOKUP to a maximum of 6. It won't change when you copy down. Change that 6 to maybe 8 as I assume your Column B will have Sat as the last entry.

 

[auhom_me]

Thanks. But as far as I'm concern this problem can easily be solved using Paste Special. But unfortunately I can't.

 

[BrianJN1]

Thanks for the feedback.
I note that you preferred something that was not VBA, PasteSpecial is a VBA element.

Do you want to go there?

 

[auhom_me]

Ok . Then Teach me :P

 

[BrianJN1]

Ok.
Right at the moment I do have some pressing family issues. Here is a rather simplistic code, not yet completed, but gives an idea of my direction.
I do need to condense that with other constructs within a FOR ... NEXT environment. Unfortunately I won't be able to follow this further for at least 16 hours.

In the meantime I welcome anyone else to address this further.

Sub Triples()
'
' Triples Macro
'

'
    Range("A2:B2").Select
    Selection.Copy
    Range("E2").Select
    ActiveSheet.Paste
    Range("E3").Select
    ActiveSheet.Paste
    Range("E4").Select
    ActiveSheet.Paste
    Range("A3:B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E5").Select
    ActiveSheet.Paste
    Range("E6").Select
    ActiveSheet.Paste
    Range("E7").Select
    ActiveSheet.Paste
End Sub

 

[BrianJN1]

Ok, I'm back with a simple SUB that does exactly as you require. Copy the code into the VBA editor and run it.

Sub Build()
a = 1
For b = 2 To 8
Range("A" & b & ":B" & b).Select
Selection.Copy
For c = 1 To 3
Range("E" & a).Select
ActiveCell.PasteSpecial
a = a + 1
Next
Next
End Sub

The first FOR ...NEXT loop selects the first two cells in Row2 which are then pasted into consecutive rows 3 times before moving to the next A:B selection.
The "a" keeps track of which row in column E to paste the A:B selection. Because two cells are selected it is only necessary to select one cell as the destination as the pasting automatically overflows to the adjacent cell.

Enough info?