Loop required for a macro

[Vishaal]

Hi,

Please check the following macro code and advice how can we run it in loop,

Sub Loop()
'
' Loop Macro
'
    Range("D2:D8").Select
    Selection.Copy
    Range("E8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F2:F8").Select
    Selection.Copy
    Range("G8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("E8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[rpaulson]

try this.

    For c = 4 To 10 Step 2 'adjust as neeed
    Range(Cells(8, c + 1), Cells(14, c + 1)).Value = Range(Cells(1, c), Cells(7, c)).Value
    Next c

hth,
Ross

 

[Vishaal]

tried not working,

we are using this as per following

Sub Macro2()
'
' Macro2 Macro
'


For c = 4 To 10 Step 2 'adjust as neeed
    Range(Cells(8, c + 1), Cells(14, c + 1)).Value = Range(Cells(1, c), Cells(7, c)).Value
    Next c
End Sub

its right process or not, confirm pls

 

[Vishaal]

pls check, its not working

 

[mohadin]

Hi
The code working Ok

Sub test()
[COLOR=#40e0d0]Dim c As Long ' [/COLOR]may be missing if Option Explicit
For c = 4 To 10 Step 2 'adjust as neeed
    Range(Cells(8, c + 1), Cells(14, c + 1)).Value = Range(Cells(1, c), Cells(7, c)).Value
    Next c
End Sub

 

[Vishaal]

Hi
The code working Ok

Sub test()
[COLOR=#40e0d0]Dim c As Long ' [/COLOR]may be missing if Option Explicit
For c = 4 To 10 Step 2 'adjust as neeed
    Range(Cells(8, c + 1), Cells(14, c + 1)).Value = Range(Cells(1, c), Cells(7, c)).Value
    Next c
End Sub

Hi Mohadin,

We have change the procedure so that we can do in fast, We have done the following step by step

1. We have used the formula in D8 =IF($C8 = "", "", IF(ISNUMBER(MATCH($C8,D$1:D$5,0)),"","Check")) and after that we drag & drop it till D16
2. After that we have used the formula in E8 =IF(AND(D8="Check",D9=""),COUNTIF(D$1:D8,"Check")-SUM(E$7:F7),"") and after that drag & drop it till F16

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

387​

[/td][td]

50000​

[/td][td]

0​

[/td][td]

50000​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

50​

[/td][td]

960​

[/td][td]

50​

[/td][td]

960​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

125​

[/td][td]

514​

[/td][td]

125​

[/td][td]

514​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

220​

[/td][td]

265​

[/td][td]

220​

[/td][td]

265​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

40000​

[/td][td]

62​

[/td][td]

40000​

[/td][td]

62​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

S.No.​

[/td][td]

Month​

[/td][td]

Amount​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1​

[/td][td]

Jan-15​

[/td][td]

514​

[/td][td]

Check​

[/td][td]

1​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

2​

[/td][td]

Feb-15​

[/td][td]

220​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

3​

[/td][td]

Mar-15​

[/td][td]

125​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4​

[/td][td]

Apr-15​

[/td][td]

125​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

5​

[/td][td]

May-15​

[/td][td]

62​

[/td][td]

Check​

[/td][td]

1​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

6​

[/td][td]

Jun-15​

[/td][td]

220​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

7​

[/td][td]

Jul-15​

[/td][td]

50​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

8​

[/td][td]

Aug-15​

[/td][td]

960​

[/td][td]

Check​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

9​

[/td][td]

Sep-15​

[/td][td]

265​

[/td][td]

Check​

[/td][td]

2​

[/td][td][/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Now we are trying to do it in one step in stead of two, we mean that in the above sheet we have used first formula in D and second in E, now we want a VBA/Formula who can show the second formula result in column D

Result Sheet are
Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

387​

[/td][td]

50000​

[/td][td]

0​

[/td][td]

50000​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

50​

[/td][td]

960​

[/td][td]

50​

[/td][td]

960​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

125​

[/td][td]

514​

[/td][td]

125​

[/td][td]

514​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

220​

[/td][td]

265​

[/td][td]

220​

[/td][td]

265​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

40000​

[/td][td]

62​

[/td][td]

40000​

[/td][td]

62​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

S.No.​

[/td][td]

Month​

[/td][td]

Amount​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

1​

[/td][td]

Jan-15​

[/td][td]

514​

[/td][td]

1​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

2​

[/td][td]

Feb-15​

[/td][td]

220​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

3​

[/td][td]

Mar-15​

[/td][td]

125​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

4​

[/td][td]

Apr-15​

[/td][td]

125​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

5​

[/td][td]

May-15​

[/td][td]

62​

[/td][td]

1​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

6​

[/td][td]

Jun-15​

[/td][td]

220​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

7​

[/td][td]

Jul-15​

[/td][td]

50​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

8​

[/td][td]

Aug-15​

[/td][td]

960​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

9​

[/td][td]

Sep-15​

[/td][td]

265​

[/td][td]

2​

[/td][td]

​

[/td][td][/td][td]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


help pls

 

[Joe4]

We have change the procedure so that we can do in fast, We have done the following step by step

1. We have used the formula in D8 =IF($C8 = "", "", IF(ISNUMBER(MATCH($C8,D$1:D$5,0)),"","Check")) and after that we drag & drop it till D16
2. After that we have used the formula in E8 =IF(AND(D8="Check",D9=""),COUNTIF(D$1:D8,"Check")-SUM(E$7:F7),"") and after that drag & drop it till F16

There is a really easy way to do this.
1. Turn on your Macro Recorder, and record yourself manually entering these equations into cells D8 and E8.
2. When finished, edit the VBA code so that you are entering the formula in the range "D8:D16" all at once, instead of "D8" (and do the same for the other formula).

So, if you turn on your Macro Recorder and record yourself entering the formula into cell D8, you will get VBA code that looks like this:

    Range("D8").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC3 = """", """", IF(ISNUMBER(MATCH(RC3,R1C:R5C,0)),"""",""Check""))"

Because the Macro Recorder is so literal, it records the cell selection, which is unnecessary, and this two lines can be combined to one line like this:

   Range("D8").FormulaR1C1 = _
        "=IF(RC3 = """", """", IF(ISNUMBER(MATCH(RC3,R1C:R5C,0)),"""",""Check""))"

Instead of using auto-fill to populate all the other cells in that column, if you know how far down you want to go, you can simply apply the formula to the entire range at once.
So, just edit the range to apply the formula to:

    Range([COLOR=#ff0000]"D8:D16"[/COLOR]).FormulaR1C1 = _
        "=IF(RC3 = """", """", IF(ISNUMBER(MATCH(RC3,R1C:R5C,0)),"""",""Check""))"

That's it! You made Excel do most of the work for you!
You can do the exact same thing with the other formula.

 

[Vishaal]

its a very good lines

"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

 

[Vishaal]

we know it but we cant make it in loop like your reply in thread, if you can do a small guidance we can do it

https://www.mrexcel.com/forum/excel-questions/1108598-loop-copy-paste-4.html

Sub copy_formula()           its vba name
    Application.ScreenUpdating = False              its showing that the screen will not change during the process
    With ActiveSheet                               We have selected the sheet
        Set Rng = Range("a12:a24")           We have selected the range
        Rng.Copy                                      We have copy the range
        For i = 2 To 50                               We have given the instruction, how many times it will do 
            .Cells(12, i).PasteSpecial Paste:=xlPasteFormulas                  we have done paste special through it
            With .Cells(12, i).Resize(13)
                .Value = .Value
            End With
        Next
    End With
End Sub

but how can we do this in the current thread, because we want to first copy formula from two columns and after that cut from second column and paste it on first column and do it untill the last value or column

 

[Vishaal]

we mean how we do

Rng = Range("a12:b24")
Rng.copy
For i = 2 To 50
.Cells(12, i).PasteSpecial Paste:=xlPasteFormulas
With .Cells(12, i).Resize(13)
.Value = .Value

now we want to cut the value from column b and paste value in column a in loop