Repeat code in VBA

Question

I have the following code that I need to repeat 1000 times:

Option Explicit

Sub Turn()


Range("f2").Select
If Range("e2").Value = "00/00/00" Then
    ActiveCell.Value = 0

ElseIf Range("e2").Value Then
    ActiveCell.Value = Range("e2")

End If

Range("f2").Select
    If ActiveCell.Value > 0 Then
        Range("G2") = Range("f2") - Range("b2")

End If
End Sub

I am new so I don't know if it is the most elegant solution to my problem, but it does the job. The problem is that I need the same code for 1000 rows and it seems a mighty task to change the cell number manually that many times.

Can You help me solve my problem?

I appreciate all help, thanks in advance.

IIJHFII · Accepted Answer · 2016-04-21T10:05:52.223

3

you could achieve it using a 'for loop'. This should be on the right lines:

Option Explicit

Sub Turn()

Dim i As Long

For i = 2 to 1001
    Range("f" & i).Select
    If Range("e" & i).Value = "00/00/00" Then
        ActiveCell.Value = 0

    ElseIf Range("e" & i).Value Then
        ActiveCell.Value = Range("e" & i)
    End If

    Range("f" & i).Select
        If ActiveCell.Value > 0 Then
            Range("G" & i) = Range("f" & i) - Range("b" & i)
        End If
Next i

End Sub

Try it out and see where you get, let us know how it goes :)

edited Apr 21 '16 at 10:05

answered Apr 21 '16 at 08:45

IIJHFII

600
1
7
23

1

Looking at his original ranges I would run `i = 2 to 1001` – Tim Wilkinson Apr 21 '16 at 09:07
1

I believe you are correct :D Thanks for the spot Tim!! – IIJHFII Apr 21 '16 at 09:08
I have tried it and i get a **Compile Error Variable Not Defined** for **i**. Also I need all cell numbers to be repeated – Niko Apr 21 '16 at 10:04
Ah yes, you need to Dim i As long, I shall add that as and edit now! What do you mean by all cell numbers repeated? – IIJHFII Apr 21 '16 at 10:05
This solved the problem!!! Thank you very much @IIJHFII . I should read more about the function you used in this – Niko Apr 21 '16 at 10:09

score 3 · Answer 2 · answered Apr 21 '16 at 15:38

3

This will be much quicker with an array:

Sub Recut()
Dim X, Y
Dim lngCnt As Long

X = [F2:G1001].Value2
Y = [B2:B1001].Value2


For lngCnt = 1 To UBound(X)
 If X(lngCnt, 1) = "00/00/00" Then
    X(lngCnt, 1) = 0
 Else
    If X(lngCnt, 1) > 0 Then X(lngCnt, 2) = X(lngCnt, 1) - Y(lngCnt, 1)
 End If
Next

[F2:G1001].Value2 = X

End Sub

answered Apr 21 '16 at 15:38

brettdj

54,857
16
114
177

It's always sad when the best answer isn't chosen because it's too sophisticated :/ Also, arrays are sexy ^_^ – findwindow Apr 22 '16 at 22:46

score 0 · Answer 3 · edited May 23 '17 at 12:16

0

This is for your learning that you should avoid .Select in your code.

Pls have a look here

pls see the below simplified code.

Sub Turn()
    Dim i As Long
    For i = 2 To 1001
        If Range("F" & i).Value = "00/00/00" Then
            Range("F" & i).Value = 0
        ElseIf Range("F" & i).Value > 0 Then
            Ramge("G" & i).Value = Range("F" & i).Value - Range("B" & i).Value
        End If
    Next i
End Sub

edited May 23 '17 at 12:16

Community

1
1

answered Apr 21 '16 at 09:47

Karthick Gunasekaran

2,697
1
15
25

@Niko, please mark as solution if you get desired result – Karthick Gunasekaran Apr 21 '16 at 11:17

score -3 · Answer 4 · answered Apr 21 '16 at 09:03

-3

Don't use "A1" style cell addresses but Cell(Row, Col) instead ...

answered Apr 21 '16 at 09:03

Rob

65
5

1

How does this answer the question? Use comments instead. – Tom Apr 21 '16 at 09:14
@Tom It answers the question very much like the other answer above, I believe. Except I didn't give the complete code. The OP asked how to vary the row number of his cell references in his code, and my answer provides a way. – Rob Apr 21 '16 at 09:30
No it doesn't. What you've suggested is replacing the OP's `A1` referencing for `R1C1` referencing. Without any mention of a loop like the other answer - the OP has still the exact same issue. – Tom Apr 21 '16 at 09:33
@Tom ok, I could have suggested the possibility of using a loop (if that's what the OP needs - it wasn't made explicit) or use some other set of numeric row values. Granted. It didn't really occur to me that this would be an issue. But downvoting because of that? Seriously? So, do you do that sort of thing all day? – Rob Apr 21 '16 at 11:15
Yup I sure do. And by the looks of it I'm not the only one that agrees. – Tom Apr 22 '16 at 09:53

Repeat code in VBA

4 Answers4