Sign in to follow this  
Followers 0
pezo89

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

5 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

yea but i would like it to be intergrated with my script, so i dont need to "change" that number manualy.

Share this post


Link to post
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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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.

Share this post


Link to post
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
Sign in to follow this  
Followers 0

  • Similar Content

    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.