Moving between columns (dynamic range?)

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


    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
    1. If Round(((ws.Range("AO79").Value - ws.Range("AO78").Value) + (ws.Range("AO79").Value < ws.Range("AO78").Value)) / 2400, 5) = 0 Then
    2. ws.Range("AO81").Value = "1"
    3. 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.

  • Re: Moving between columns (dynamic range?)


    Try the code below
    You had a zillion if's when you really needed just one. You also had quotation marks surrounding the values you were entering if the condition was met, doing that changes the number value to text.


  • Re: Moving between columns (dynamic range?)


    Thanks for your help. I am trying to get the hang of the next function. I tried the code but i still get the same run time error. It says type mismatch 13.

  • Re: Moving between columns (dynamic range?)


    Thanks for all your help. The vba works incredibly fast.


    Cells 78 and 79 contain array formulas that return military times. I kept trying to put 'if' formulas on cells 81 that would return the '1-13' values but I could not get it to work, I think it was because of the array formulas.


    In a cell (80) formatted as minutes [m], the formula returns the time difference between cells 78 and 79. So the first formula would return 0 if the times are the same in both 78 and 79.


    The error was just in the first if statement, like Pike stated. So in the first 'if' statement instead of running the same formula, i changed it to if they are the same values then return 1.


    Code
    1. If ws.Cells(oRow, i).Value = ws.Cells(iRow, i).Value Then