1

I am not sure where to start with the programming. I have a column of Excel data that has non-consecutive zeros distributed in the array. I want to build another column of data by selecting the number immediately before the zeros. Here is a sample data set (I want to pick out 1, 4, and 6):

    5
    1
    0
    4
    0
    820
    4
    6
    0
    74

    Sub Cat()   
     
          Range("V12").Select
    
          Do Until IsEmpty(ActiveCell)
    
             ActiveCell.Offset(1, 0).Select
          Loop
       
    End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Kisheon
  • 43
  • 7
  • 1
    Does your version of Excel support Dynamic Array formulas? This is very easy with `FILTER` for example. – BigBen Mar 22 '23 at 19:17
  • Side note: In general, you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Mar 22 '23 at 19:22

2 Answers2

1

Please, try the next way. It assumes that the range to be processed exists in A:A column and the resulted array will drop its content in B:B, starting from B1:

Sub ExtractBeforeZero()
  Dim sh As Worksheet, lastR As Long, arr, arrB0, i As Long, k As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
  
  arr = sh.Range("A1:A" & lastR).Value2 'place the range in an array for faster  processing
  ReDim arrB0(1 To UBound(arr)) 'redim the array to keep the return of its maximum possible number of elements
  k = 1
  
  For i = 1 To UBound(arr)
        If arr(i, 1) = 0 Then arrB0(k) = arr(i - 1, 1): k = k + 1
  Next i
  
  If k > 1 Then 'if at least a zero has been found:
    ReDim Preserve arrB0(1 To k - 1) 'keep only the filled elements
    'drop the resulted array:
    sh.Range("B1").Resize(UBound(arrB0), 1).Value2 = Application.Transpose(arrB0)
  Else
     MsgBox "No zero could be found in the processed column..."
  End If
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • 2
    Please please **never** use the One-Line If-Statement and then put more than one statement behind it. That makes a rather simple statement so hard to read. – FunThomas Mar 22 '23 at 19:38
  • 1
    @Scott Craner Of course, I tested the code, copied here and tried commenting. But placed `:` instead of `'`. Corrected it. Thanks! – FaneDuru Mar 22 '23 at 19:56
  • @FunThomas I would not agree it. I would say on contrarious, in such a context a simple code line followed by the used variable increment is more eloquent. At least, on my taste... – FaneDuru Mar 22 '23 at 20:00
1

The Do...Loop: A Basic Example

  • This is a way to build the logic using a Do...Loop.
  • It is rather inefficient (slow) and unflexible i.e. it assumes that there are no consecutive zeros, that the first source cell is not zero, and that the source range is continuous and filled with numbers.
  • It will write the result in column W.

A Quick Fix

Option Explicit

Sub Cat()
 
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = Activesheet ' improve!
    
    ' Reference the 2nd source cell.
    Dim sCell As Range: Set sCell = ws.Range("V12").Offset(1)
    
    ' Reference the 1st destinatin cell.
    Dim dCell As Range: Set dCell = ws.Range("W12")
    
    Do Until IsEmpty(sCell.Value)
        If sCell.Value = 0 Then
            dCell.Value = sCell.Offset(-1).Value ' ... = previous source cell
            Set dCell = dCell.Offset(1) ' ... = next destination cell
        End If
        Set sCell = sCell.Offset(1) ' ... = next source cell
    Loop
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28