How to make my code faster?

Hello chrisignm,

When working with Ranges, you can increase execution speed by limiting your Range size to only cells that have information. Declaring a Range as a full row or column will take much longer to process than just the cells that have data,

Chris,

Yeah Leith is correct. It may be helpful to loop over cells in the b.UsedRange, where b is defined as the worksheet in your code. But I've never seen such a huge If-ElseIf statement.

It also looks like you're trying to implement worksheet functions in VBA. I think you can speed up your code by doing whatever your worksheet function does in VBA code instead of the worksheet function with the Evaluate() funciton.

What are you trying to do? What does "Number" and "WkRow1"..."WkRow19" represent?

In order to loop over cells in a range, the structure looks like:

For Each C In b.UsedRange
'/Do something here
'/C.Value returns the value in the cell
Next C

Joe

Not sure about speeding the code up, though Leith does have a good point regarding ranges, but couldn't you cut the code down to this?

Sub ChangeValuetoOne()
Dim Number As Long
Dim WkRow
Dim b As Worksheet
Dim I As Long

    Set b = ActiveWorkbook.Worksheets("Stock_List")

    Number = Worksheets("Calc2").Range("V1").Value

    For I = Number + 1 To 2 Step -1

        WkRow = Evaluate("MATCH(Calc2!J" & I & "& Calc2!L" & I & ",Stock_List!$A:$A&Stock_List!$C:$C,0)")
        b.Cells(WkRow, "D") = 1
        b.Cells(WkRow, "F") = 4

    Next I

End Sub

In the code in the link you were going from Number + 1 down to 2.

For example if Number was 2 the code went from 3 to 2, if Number was 10 the code went from 11 to 2 and so on.

I'm confused, for Number = 1 & 2 this was the code.

If Number = 1 Then
   WkRow1 = Evaluate("MATCH(Calc2!J2 & Calc2!L2,Stock_List!$A:$A&Stock_List!$C:$C,0)")
   b.Cells(WkRow1, "D") = 1
   b.Cells(WkRow1, "F") = 4
ElseIf Number = 2 Then
   WkRow2 = Evaluate("MATCH(Calc2!J3 & Calc2!L3,Stock_List!$A:$A&Stock_List!$C:$C,0)")
   b.Cells(WkRow2, "D") = 1
   b.Cells(WkRow2, "F") = 4
   WkRow1 = Evaluate("MATCH(Calc2!J2 & Calc2!L2,Stock_List!$A:$A&Stock_List!$C:$C,0)")
   b.Cells(WkRow1, "D") = 1
   b.Cells(WkRow1, "F") = 4
Elseif Number = 3 Then
...

No problem.