count closest empty cells in a row
 

Hi

if in A1 is a value , b1 empty , c1 empty ,
in d1 my formula i need must return value 2 ( it found
2 empty cells column C to the left ) .

My database i have to update every day , is from A11 to A82160 ,
but never have the same edge , data can be for eg
in row A11 to AB11 , A12:M12 , A13:AD12 ,
row by row is randomly different ;

is there a way to can have a formula ?

the formula I will enter in the same column ,
from CS11 to CS 82170 .many thanks

count closest empty cells in a row
 

Hi,

Am Tue, 1 Dec 2020 12:51:12 -0800 (PST) schrieb Xxer Xxes:

if in A1 is a value , b1 empty , c1 empty ,
in d1 my formula i need must return value 2 ( it found
2 empty cells column C to the left ) .

My database i have to update every day , is from A11 to A82160 ,
but never have the same edge , data can be for eg
in row A11 to AB11 , A12:M12 , A13:AD12 ,
row by row is randomly different ;

is there a way to can have a formula ?

the formula I will enter in the same column ,
from CS11 to CS 82170 .many thanks

try it in CS11 with
=COUNTBLANK(OFFSET($A11,,,,SUMPRODUCT(MAX((A11:CR1 1<"")*COLUMN(A:CR)))))


Regards
Claus B.
--
Windows10
Office 2016

count closest empty cells in a row
 

=COUNTBLANK(OFFSET($A11,,,,SUMPRODUCT(MAX((A11:C R11<"")*COLUMN(A:CR)))))

It works very well for me ; thank you very much for
your assistence , Sir !

Try this:
 

Option Explicit

Sub subtractLastValueRow()
' declare vars
Dim oSht As Worksheet ' work sheet
Dim a As Variant ' one based 2-dim data field array
Dim n As Long ' last row in column B
Dim i As Long ' item no
Dim ii As Long ' last item no
Dim j As Long
Dim s As String
' set sheet
Set oSht = ThisWorkbook.Worksheets("MySheet") ' fully qualified reference to worksheet
' get last row number of search column
n = oSht.Range("B" & oSht.Rows.Count).End(xlUp).Row
If n < 2 Then Exit Sub ' only if data avaible (row 1 = title line)

' get range (after title line) values to one based 2dim data field array
a = oSht.Range("B2:C" & n).Value ' array gets data from e.g. "A2:A100"
' loop through column B to find keyword sKey
If Len(a(1, 1) & "") 0 Then ii = 1 + 1 ' first item in array
For i = LBound(a) + 1 To UBound(a) ' array boundaries counting from 1+1 to n -1 (one off for title line)
' value found
If Len(a(i, 1) & "") 0 Then
For j = i + 1 To UBound(a)
If Len(a(j, 1) & "") 0 Then
' write .Formula (alternatively use .Value, if value wanted)
oSht.Range("C" & i + 1).Formula = "=B" & i + 1 & "-B" & ii
ii = i + 1 ' note last found i
Exit For
End If
Next j
End If
Next
If Len(a(UBound(a), 1) & "") 0 Then ' last item in array
oSht.Range("C" & UBound(a) + 1).Formula = "=B" & UBound(a) + 1 & "-B" & ii
End If
End Sub

You can edit the values accordingly.

Hope this helps.

Regards,
Jerry

count closest empty cells in a row
 

Thank you very much for your response , Sir ;
it helps me , of course .

I wish you all a very very good new Year .