<Noobie>Excel first script

[water]

Use this line I already posted:

_Excel_RangeWrite($oWorkbook, Default, "=Mod(A2;2)", "$B2:B" & $oWorkbook.Activesheet.UsedRange.Rows.Count, False) ; Insert the formula (modulo) in column B

replace "=Mod(A2;2)" with your formula. The example will copy the formula to all used cells in column B. As all cell referenced in the formula are relative the copy operation will adjust the reference.

[zsoltm]

its doesnt seems to me to doing anything...

[water]

its doesnt seems to me to doing anything...

... doesn't really help me to find the cause of the problem

[zsoltm]

How can I make it automatically determine the number of the rows (in the excel documents) to be able to set the parameters for the script?

#include <Excel Rewrite.au3>


Global $i = 1
Global $selector = '"of","restaurant","courtyard","licenced","landscapes","travel","specialist","testing","relay","imaging","environmental","associates"'
Global $sFormula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"


Global Const $xlCellTypeVisible = 12 ; All visible cells
Global $aData = $sFormula
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookNew($oExcel) ; Create a new workbook


For $i = 0 To 100 step 1
Next


_Excel_RangeWrite($oWorkbook, Default, $aData, "A1:A100");
MsgBox(0, "RangeWrite Data", @error)
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 1, "NO") ; Only display even numbers
MsgBox(0, "FilterSet", @error)
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
MsgBox(0, "Visible cells", @error)
$oRange.EntireRow.Delete ; delete visible rows

[water]

The number of used rows can be determined by

$oWorkbook.Activesheet.UsedRange.Rows.Count

[zsoltm]

expandcollapse popup

#include <Excel Rewrite.au3>


Global $i = 1
Global $selector = '"hairdressing","ltd","services","furnitures","co","inn","national","testing","superstore","shop","society","club"'
Global $sFormula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"




Global Const $xlCellTypeVisible = 12 ; All visible cells
Global $aData = $sFormula
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
Global $oWorkbook = _Excel_BookOpen;_Excel_BookNew($oExcel)


Global $row = $oWorkbook.Activesheet.UsedRange.Rows.Count


_Excel_RangeInsert($oWorkbook.Activesheet, "A:A", $xlShiftToRight) ; Insert column B and shift existing data to the right




For $i = 0 To $row step 1
Next




_Excel_RangeWrite($oWorkbook, Default, $aData, "A1:A"$row);
MsgBox(0, "RangeWrite Data", @error)
_Excel_FilterSet($oWorkbook, Default, $oWorkbook.Activesheet.UsedRange, 1, "Yes") ; Only display even numbers
MsgBox(0, "FilterSet", @error)
Global $oRange = $oWorkbook.Activesheet.UsedRange.SpecialCells($xlCellTypeVisible) ; Only select visible rows
MsgBox(0, "Visible cells", @error)
$oRange.EntireRow.Delete ; delete visible rows

I made a quick example. is something wrong with my codes. there is 70 row so the $row should be = 70. but now it dont want to fill in my formula

http://speedy.sh/wJrtm/example.xlsx

[water]

Will do some tests as soon as I return to my office.

[water]

The following example works but Excel seems to have a problem with your formula. Can you try to manually insert the formula?

#include <Excel Rewrite.au3>

Global $selector = '"hairdressing","ltd","services","furnitures","co","inn","national","testing","superstore","shop","society","club"'
;Global $sFormula = "=IF(OR(ISNUMBER(search("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"
Global $sFormula = "=WENN(ODER(ISTZAHL(SUCHE("" ""&{" & $selector & "}&"" "","" ""&b" & $i & "&"" ""))),""YES"",""NO"")"
ConsoleWrite($sFormula & @LF)

Global Const $xlCellTypeVisible = 12 ; All visible cells
Global $aData = $sFormula
Global $oExcel = _Excel_Open() ; Connect to Excel or open a new instance
MsgBox(0, "Open", @error)
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\example.xlsx")
MsgBox(0, "Bookopen", @error)
Global $row = $oWorkbook.Activesheet.UsedRange.Rows.Count
_Excel_RangeInsert($oWorkbook.Activesheet, "A:A", $xlShiftToRight) ; Insert column B and shift existing data to the right
MsgBox(0, "RangeInsert", @error)
_Excel_RangeWrite($oWorkbook, Default, "=MOD(A1;2)", "A1:A" & $row, False)
;_Excel_RangeWrite($oWorkbook, Default, $sFormula, "A1:A" & $row, False)
MsgBox(0, "RangeWrite Data", @error)