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

[pezo89]

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

expandcollapse popup

;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 February 15, 2013 by pezo89

[water]

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

[pezo89]

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

[water]

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

Use functions _ExcelReadArray or _ExcelReadSheetToArray.

[jdelaney]

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 February 15, 2013 by jdelaney