Jump to content

how to count numbers of cell that has something in it?


pezo89
 Share

Recommended Posts

Hi, i am working on little personal code that is based upon an excel list

but what is the easiest way to count how many cells in A that has something in it?

this is to be used in "

For $cellrow = 2 To 10;how to make it count max rows that has something in it?

"

where 10 is beeing changed to the number of cells that has something in it :)

;step by step
;1) Open Excel book
;2) Activate correct sheet
;3) Create Internet explorer windows with Cellvalue
;4) Focus IE,and verify that options are existing ;Check if item is still in stock, (not deleted)
;
#include
#include
Local $sFilePath1 = @ScriptDir & "/Autentisering av produkter.xlsx" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1)
_ExcelSheetActivate($oExcel, "ClothingShowroom")

If @error = 1 Then
MsgBox(0, "Error!", "Unable to Create the Excel Object")
Exit
ElseIf @error = 2 Then
MsgBox(0, "Error!", "File does not exist - Shame on you!")
Exit
EndIf
$ieWindow = _IECreate("about:blank");create Main operation window
;3) and wait for it to load
For $cellrow = 2 To 10;how to make it count max rows that has something in it?
$sCellValue = _ExcelReadCell($oExcel, $cellrow, 1)
_IELoadWait($ieWindow);wait for operation
_IENavigate($ieWindow, $sCellValue);Enter website from Cell $i
_IELoadWait($ieWindow);wait for operation
;4)



Local $sText = _IEBodyReadText($ieWindow)

If StringInStr($sText, "No products match your search criteria, please try again. ") Then
;;; I found it

_ExcelWriteCell($oExcel, "Ikke på lager", $cellrow, 2) ;Write to the Cell
Else
;;; didn't find it
_ExcelWriteCell($oExcel, "På lager", $cellrow, 2) ;Write to the Cell

If StringInStr($sText, "Small+#+55") Then
_ExcelWriteCell($oExcel, "OK", $cellrow, 3) ;Write to the Cell
else
_ExcelWriteCell($oExcel, "Nei", $cellrow, 4) ;Write to the Cell
_IELoadWait($ieWindow);wait for operation

EndIf
_IELoadWait($ieWindow);wait for operation
If StringInStr($sText, "Medium+#+56") Then
_ExcelWriteCell($oExcel, "OK", $cellrow, 4) ;Write to the Cell
else
_ExcelWriteCell($oExcel, "Nei", $cellrow, 4) ;Write to the Cell
_IELoadWait($ieWindow);wait for operation
EndIf

If StringInStr($sText, "Large+#+57") Then
_ExcelWriteCell($oExcel, "OK", $cellrow, 5) ;Write to the Cell
else
_ExcelWriteCell($oExcel, "Nei", $cellrow, 5) ;Write to the Cell
_IELoadWait($ieWindow);wait for operation
EndIf



EndIf
Next


_IELoadWait($ieWindow);wait for operation
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel) ;Close Excel book after is done
_IEQuit($ieWindow)
Edited by pezo89
Link to comment
Share on other sites

You could use function "Count" in Excel. This function only counts non empty cells.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

In this case you need to read the content into an array and count the cells.

Use functions _ExcelReadArray or _ExcelReadSheetToArray.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

all mighty google:

excel vba count non empty cells in column

link 1: n = Worksheets("Sheet1").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

You need to translate xlcelltypeconstants to an integer. I generally just make a messagebox macro, and pass in the variable string to get it.

Edited by jdelaney
IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...