Do Until IsEmpty not working, any ideas?

Question

I'm making a simple subtraction between two cells that contain dates in order to obtain the period. Every client, in Data_p worksheet, Range ("4"), will have all the order dates in the respective column. So the subtraction will be between the second date and the first, and so on, and the result will be pasted in Data_p_mgnt. This function will have to be executed until there're no more dates for each client.

I have the following code, but I don't know why it won't stop when it finds and Empty cell in Data_p. Any insight will be appreciated.

Sub Prueba_Data_p_mgnt()

Sheets("Data_p_mgnt").Select
Range("B5").Select 'Starts in cell B5

Do Until IsEmpty(Worksheets("Data_p").Range("B5")) 'Checks if cells in Data_p are Empty or Blank

ActiveCell.FormulaR1C1 = "=Data_p!R[1]C-Data_p!RC" 'Makes the subtraction between cells
ActiveCell.Offset(1, 0).Range("A1").Select 'Moves down for paste the next period

Loop 'Loop until there's an Empty cell in Data_p

'Then should move to next client to the right and repeat until there are no more clients in row 4 

End Sub

You are always testing Range("B5"). You are not changing B5 to blank then it will loop forever. — Scott Craner, Oct 03 '16 at 20:40
@ScottCraner has it - read through [how to avoid using `.Select`/`.Activate`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and apply what you learn, that should remove the issue. To keep your code as-is, you'd want `Do Until IsEmpty(ActiveCell)`...but don't do that, use the ranges. Just look up how to loop dynamically through cells/a range. — BruceWayne, Oct 03 '16 at 20:41
Also, the `.Range("A1")` in `ActiveCell.Offset(1, 0).Range("A1")` is completely redundant. — Comintern, Oct 03 '16 at 20:47
Any ideas of how to avoid the circular references? @ScottCraner — André Mora Barreira, Oct 03 '16 at 20:49
Create the formula you want then try to use actual range references to replicate the formula. Unfortunately there are too many errors and ambiguity in your question for me to attempt a fix, that would work without much back and forth and for that I do not have the time at the moment. — Scott Craner, Oct 03 '16 at 20:51
Actually is really simple, in sheet Data_p, I will do B6-B5, B7-B6, B8-B7 until there´s a blank cell. Then it will start at C5 doing the same (C6-C5, C7-C6... until blank) The results should be seen at Data_p_mgnt sheet starting at B5 (result of B6-B5). The same for the results of column C starting at C5 (result of C6-C5) an so on for all the columns with data @ScottCraner — André Mora Barreira, Oct 03 '16 at 21:04
@ScottCraner - the ActiveCell is in one worksheet, the formula is referring to the same cell but in a different worksheet, therefore no circular reference. — YowE3K, Oct 03 '16 at 21:16
@ScottCraner - another reason to hate code using `ActiveCell` and `Select`, etc - it makes it very hard to understand exactly what is being referred to — YowE3K, Oct 03 '16 at 21:22

YowE3K · Accepted Answer · 2016-10-03T21:08:30.133

I believe this is what you are trying to do:

Sub Prueba_Data_p_mgnt()
    Dim wsMgnt As Worksheet
    Dim wsData As Worksheet
    Dim rowNo As Long
    Dim colNo As Long
    Set wsMgnt = Worksheets("Data_p_mgnt")
    Set wsData = Worksheets("Data_p")

    colNo = 2
    Do Until IsEmpty(wsData.Cells(4, colNo)) 'Checks if cells in Data_p are Empty or Blank

        rowNo = 5
        Do Until IsEmpty(wsData.Cells(rowNo, colNo)) 'Checks if cells in Data_p are Empty or Blank
        'Alternatively, to avoid subtracting the last non-blank cell from a blank cell
        'Do Until IsEmpty(wsData.Cells(rowNo + 1, colNo)) 'Checks if cells in Data_p are Empty or Blank

            wsMgnt.Cells(rowNo, colNo).FormulaR1C1 = "=Data_p!R[1]C-Data_p!RC" 'Makes the subtraction between cells
            'Alternatively, if you would rather have values than formulae
            'wsMgnt.Cells(rowNo, colNo).Value = wsData.Cells(rowNo + 1, colNo).Value - wsData.Cells(rowNo, colNo).Value

            rowNo = rowNo + 1 'Moves down for paste the next period
        Loop 'Loop until there's an Empty cell in Data_p

        colNo = colNo + 1 'Then should move to next client to the right and repeat until there are no more clients in row 4 
    Loop

End Sub

@AndréMoraBarreira - I don't think your logic will work well on the last cell in each column (you will be subtracting the last date from the following empty cell), so I have just edited the answer to provide an alternative to avoid that. — YowE3K, Oct 03 '16 at 21:09
Yes that´s perfect, I was planning to use an IF for deleting those numbers but what you proposed is better — André Mora Barreira, Oct 03 '16 at 21:13

score 1 · Answer 2 · answered Oct 03 '16 at 20:48

This should, I hope, do the trick:

Sub Prueba_Data_p_mgnt()
Dim dataWS As Worksheet
Dim rng As Range
Set dataWS = Sheets("Data_p_mgnt")

Set rng = dataWS.Range("B5") ' what's this? You never use data_p_mgnt cell B5?

For i = 5 To 100 ' Change 100 to whatever you need
    If Not IsEmpty(Worksheets("Data_p").Range("b" & i)) Then 'Checks if cells in Data_p are Empty or Blank
        Worksheets("Data_p").Range("b" & i).FormulaR1C1 = "=Data_p!R[1]C-Data_p!RC" 'Makes the subtraction between cells
    End If 'Loop until there's an Empty cell in Data_p
Next i
End Sub

But looking at your code, I don't know what you plan on doing with B5 on the Data_p_mgnt sheet.

The data I´m using is located in Data_p starting in cell B5. The subtraction will go B6-B5, B7-B6, B8-B7 until there´s a blank cell. The results of this subtractions are listed in Data_p_mgnt staring at cell B5 — André Mora Barreira, Oct 03 '16 at 20:55

Do Until IsEmpty not working, any ideas?

2 Answers2