Jump to content
Sign in to follow this  
pezo89

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

Recommended Posts

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 :)

;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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
pezo89

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
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 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  

  • Similar Content

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×