Moving between columns (dynamic range?)

peerogel · Mar 12th 2016

I cant figure how to move between columns applying an "if" formula. I finally managed to move between columns applying a value.

Code

Sub test1()
   Dim ws As Worksheet
   Set ws = ActiveSheet
Dim iRow As Integer, i As Integer
iRow = 90
oRow = iRow + 1
uRow = iRow + 3


For i = 39 To 52
ws.Cells(iRow, i).Value = 1
ws.Cells(oRow, i).Value = 2
ws.Cells(uRow, i).Value = 3
Next i
End Sub

Display More

But I have been trying to figure how to apply an "if" formula between the columns and I am totally lost.
This is the formula:

Code

If Round(((ws.Range("AO79").Value - ws.Range("AO78").Value) + (ws.Range("AO79").Value < ws.Range("AO78").Value)) / 2400, 5) = 0 Then
   ws.Range("AO81").Value = "1"
   Else

So I came up with this but it does not work at all. I get a run time error 13. Thanks in advance for any help.

Code

Sub TEST22()
   Dim ws As Worksheet
   Set ws = ActiveSheet
Dim iRow As Integer, oRow As Integer, uRow As Integer, i As Integer
iRow = 78
oRow = iRow + 1
uRow = iRow + 1


For i = 39 To 52
    
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0 Then
   ws.Cells(uRow, i).Value = "1"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.00208 Then
   ws.Cells(uRow, i).Value = "2"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.00417 Then
   ws.Cells(uRow, i).Value = "3"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.00625 Then
   ws.Cells(uRow, i).Value = "4"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.00833 Then
   ws.Cells(uRow, i).Value = "5"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.01042 Then
   ws.Cells(uRow, i).Value = "6"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.0125 Then
   ws.Cells(uRow, i).Value = "7"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.01458 Then
   ws.Cells(uRow, i).Value = "8"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.01667 Then
   ws.Cells(uRow, i).Value = "9"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.01875 Then
   ws.Cells(uRow, i).Value = "10"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.02083 Then
   ws.Cells(uRow, i).Value = "11"
   Else
   If Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5) = 0.02292 Then
   ws.Cells(uRow, i).Value = "12"
   Else
   If Round(Round(((ws.Cells(oRow, i).Value - ws.Cells(iRow, i).Value) + (ws.Cells(oRow, i).Value < ws.Cells(iRow, i).Value)) / 2400, 5), 5) = 0.04167 Then
   ws.Cells(uRow, i).Value = "13"
   Else
   ws.Cells(uRow, i).Value = "13"
   End If
    End If
        End If
            End If
                End If
                     End If
                         End If
                             End If
                                End If
                                    End If
                                        End If
                                            End If
                                                End If
Next i
End Sub

Display More

philb1 · Mar 12th 2016