CODEWITHSUNDEEP
vbaexcel
Niko
Niko

Reputation: 29

Repeat code in VBA

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.

Upvotes: 1

Views: 9463

Answers (4)

brettdj
brettdj

Reputation: 55672

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

Upvotes: 3

IIJHFII
IIJHFII

Reputation: 600

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 :)

Upvotes: 3

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 0

Rob
Rob

Reputation: 65

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

Upvotes: -3

Related Questions