Loop required for a macro

Vishaal · Nov 12, 2019

Hi,

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

Code:

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 · Nov 12, 2019

try this.

Code:

    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 · Nov 12, 2019

tried not working,

we are using this as per following

Code:

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 · Nov 12, 2019

pls check, its not working

mohadin · Nov 13, 2019

Hi
The code working Ok

Code:

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 · Nov 14, 2019

mohadin said:

Hi
The code working Ok

Code:

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]

387

[/td][td]

50000

[/td][td]

0

[/td][td]

50000

[/td][/tr]

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

2

[/td][td]

50

[/td][td]

960

[/td][td]

50

[/td][td]

960

[/td][/tr]

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

3

[/td][td]

125

[/td][td]

514

[/td][td]

125

[/td][td]

514

[/td][/tr]

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

4

[/td][td]

220

[/td][td]

265

[/td][td]

220

[/td][td]

265

[/td][/tr]

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

5

[/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][/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][/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][/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][/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][/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][/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]

387

[/td][td]

50000

[/td][td]

0

[/td][td]

50000

[/td][/tr]

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

2

[/td][td]

50

[/td][td]

960

[/td][td]

50

[/td][td]

960

[/td][/tr]

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

3

[/td][td]

125

[/td][td]

514

[/td][td]

125

[/td][td]

514

[/td][/tr]

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

4

[/td][td]

220

[/td][td]

265

[/td][td]

220

[/td][td]

265

[/td][/tr]

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

5

[/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][/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][/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][/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][/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][/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][/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][/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 · Nov 15, 2019

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:

Code:

    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:

Code:

   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:

Code:

    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 · Nov 15, 2019

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 · Nov 15, 2019

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

Rich (BB code):

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 · Nov 15, 2019

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

Loop required for a macro

Vishaal

Well-known Member

Excel Facts

rpaulson

Well-known Member

Vishaal

Well-known Member

Vishaal

Well-known Member

mohadin

Well-known Member

Vishaal

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Vishaal

Well-known Member

Vishaal

Well-known Member

Vishaal

Well-known Member

Similar threads

Forum statistics

Share this page

Loop required for a macro

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock