Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 1st 20, 09:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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

  #2   Report Post  
Old December 2nd 20, 09:16 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,832
Default 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
  #3   Report Post  
Old December 2nd 20, 11:01 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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 !
  #4   Report Post  
Old December 29th 20, 04:42 PM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2020
Posts: 26
Default 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
  #5   Report Post  
Old December 29th 20, 06:32 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2020
Posts: 33
Default 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 .



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Non-Empty Cells In Each Row Bennington Excel Worksheet Functions 2 September 21st 18 07:40 AM
How to count the number of cells not empty? Eric Excel Discussion (Misc queries) 9 April 19th 10 09:46 PM
How to count non-empty cells SteveT[_2_] Excel Programming 2 October 9th 06 04:27 AM
Count Empty Cells in Range After Cells with Data David Excel Programming 16 September 17th 06 03:03 PM
count for empty cells tikchye_oldLearner57 Excel Discussion (Misc queries) 6 May 25th 06 08:41 PM


All times are GMT +1. The time now is 07:24 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017