Get the value of last cell used in row

[alien4u]

What is on the 1138 row? what make that row different?

You must understand that the total amount of row counts is based on column "A" as you see in this line of code:

$countRow = .Cells(.Rows.Count, "A").End($xlUp).row

So check column A on that row where you script stops.

Regards
Alien.

[antmar904]

The same data format in row 1138 were the script stops is the same as all other rows in colum A

[antmar904]

If I add

With $oWorkbook.ActiveSheet
    $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
ConsoleWrite("There are: " & $countRow & " rows in your workbook")
Exit

is shows the total row count correct.

There are: 11400rows in your workbook+>15:10:54 AutoIt3.exe ended.rc:0

[alien4u]

Then for debugging add a ConsoleWrite after your _ExcelRangeWrite() and print the value RangeWrite will write and the value or @error macro,

Regards
Alien.

[antmar904]

 

30) : ==> The requested action with this object has failed.:

[antmar904]

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "M"&$i, "", "")
    If @error Then
        ConsoleWrite(@error)
    EndIf

"C:\lncomputernametest.au3" (30) : ==> The requested action with this object has failed.:
$lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
$lastColvalue = ^ ERROR
->16:01:01 AutoIt3.exe ended.rc:1

[alien4u]

19 hours ago, antmar904 said:

_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "M"&$i, "", "")
    If @error Then
        ConsoleWrite(@error)
    EndIf

"C:\lncomputernametest.au3" (30) : ==> The requested action with this object has failed.:
$lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
$lastColvalue = ^ ERROR
->16:01:01 AutoIt3.exe ended.rc:1

I mean something like this:

For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
    ;MsgBox(0, "", $lastColvalue)
    $aLastComputer = StringSplit($lastColvalue, ",")
    $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
    ;MsgBox(0, "", $sLastComputer)
    ConsoleWrite("The Script about to write: "&$sLastComputer & " in: "& "D"&$i)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "D"&$i, "", "")
    If @error Then
        ConsoleWrite(@error)
    EndIf
    EndWith
Next

Anyways you need to be sure the script is always to write in to "D" Column if the position of column is different in one row then you will get an error.

Regards
Alien.

[antmar904]

The Script about to write: CANPRIWOODWOC13 in: C1038
The Script about to write: CANMISZUFICHC13 in: C1039
The Script about to write: CANSTHLABMACH14 in: C1040
The Script about to write: CANVERWERNICKM1 in: C1041
The Script about to write: CANPRITAYLORJ13 in: C1042
The Script about to write: CANEDMHALLEC11 in: C1043
The Script about to write: -usa\cccanecfw1 in: C1044
The Script about to write: CANBRACUMMINC14 in: C1045
The Script about to write: -usa\cabbnecgm1 in: C1046
The Script about to write: CANMISOSBORNC13 in: C1047
The Script about to write: CANDELELGERTC12 in: C1048
The Script about to write: CANPARWETHERC11 in: C1049
The Script about to write: USATONMASON13 in: C1050
The Script about to write: CANCALROBICHC13 in: C1051
The Script about to write: CANHAMCOOPERC13 in: C1052
The Script about to write: CANSTLLAROSEC12 in: C1053
The Script about to write: CANEDMCONSHIP13 in: C1054
The Script about to write: USATONBARTLEK11 in: C1055
!>14:27:38 AutoIt3.exe ended.rc:-1073741819
+>14:27:38 AutoIt3Wrapper Finished.
>Exit code: 3221225477    Time: 32.36

 

Autoit crashed also

Edited May 13, 2016 by antmar904

[alien4u]

@antmar904
You could add @CRLF to your consolewrite to have a better output:

ConsoleWrite("The Script about to write: "&$sLastComputer & " in: "& "D"&$i&@CRLF)

Please edit your previous post and only show last 10 lines no more than that.

Then check what is there in F1073 or F1074, check if row 1074 have something different in the structure, different number of columns or anything that could be causing the problem.

Regards
Alien.

[antmar904]

@alien4u

The Script about to write: CANPRIWOODWOC13 in: C1038
The Script about to write: CANMISZUFICHC13 in: C1039
The Script about to write: CANSTHLABMACH14 in: C1040
The Script about to write: CANVERWERNICKM1 in: C1041
The Script about to write: CANPRITAYLORJ13 in: C1042
The Script about to write: CANEDMHALLEC11 in: C1043
The Script about to write: -usa\canecfw1 in: C1044
The Script about to write: CANBRACUMMINC14 in: C1045
The Script about to write: -usa\canecgm1 in: C1046
The Script about to write: CANMISOSBORNC13 in: C1047
The Script about to write: CANDELELGERTC12 in: C1048
The Script about to write: CANPARWETHERC11 in: C1049
The Script about to write: USATONMASON13 in: C1050
The Script about to write: CANCALROBICHC13 in: C1051
The Script about to write: CANHAMCOOPERC13 in: C1052
The Script about to write: CANSTLLAROSEC12 in: C1053
The Script about to write: CANEDMCONSHIP13 in: C1054
The Script about to write: USATONBARTLEK11 in: C1055
!>14:27:38 AutoIt3.exe ended.rc:-1073741819
+>14:27:38 AutoIt3Wrapper Finished.
>Exit code: 3221225477    Time: 32.36

[water]

Maybe the cell contains some invalid data that was transfered from LN?

[antmar904]

the column before the column that list the users computer name is the users lan id.

not all rows with have a computer name in the "client machine" column.

exp:

user "test-usa\cccc" does not list a computer name in column B.

can that be the reason why the script is stopping at row 1038?

 

[water]

Maybe that those empty cells contain strange data like Null or whatever.
You could check with VarGetType.

[alien4u]

5 hours ago, antmar904 said:

the column before the column that list the users computer name is the users lan id.

not all rows with have a computer name in the "client machine" column.

exp:

user "test-usa\cccc" does not list a computer name in column B.

can that be the reason why the script is stopping at row 1038?

 

Yes I think that could be the reason because this 2 lines:

$aLastComputer = StringSplit($lastColvalue, ",")
$sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]

So here you split what is on ClientMachine column and use that to later write $sLastComputer using _ExcelRangeWrite.
So something like this should help:

If IsArray $aLastComputer Then
    $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
    _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "D"&$i, "", "")
Else
    ConsoleWrite("Skiping row: "&$i &" because there is no Computer Name to Write")
EndIf

You really need to study and understand what are you doing because if you don't do that then your problems will never end...

Regards
Alien.
 

[antmar904]

Thank you both for your input however if I remove all blank Client Machine rows the script still stops around the 1053 row.

[water]

On 14.5.2016 at 2:40 AM, alien4u said:

If IsArray $aLastComputer Then
    $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
    _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "D"&$i, "", "")
Else
    ConsoleWrite("Skiping row: "&$i &" because there is no Computer Name to Write")
EndIf

You shouldn't remove the empty rows but test what alien4u suggested above. Does it work then?

[antmar904]

@water

I ran the script again and it's still stopping around row 1037.

Its also entering the value of the last cell and not skipping (in this case the value of cell A).

 

"C:\lncomputernametest.au3" (20) : ==> The requested action with this object has failed.:
$lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
$lastColvalue = ^ ERROR

Edited May 18, 2016 by antmar904

[antmar904]

Hi All,

It's been awhile that I have not looked at this thread or script I've been working on.

The script still stops around row 1022.

The script also writes the value of the first column (A) even if there is no other data in that row. 

We have: 

If IsArray ($aLastComputer) Then
            $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
            _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "C" & $i, "", "")
        Else
            ConsoleWrite("Skiping row: " & $i & " because there is no Computer Name to Write")
        EndIf

But this is not working.

Here is the output:

"C:\Temp\lncomputernametest.au3" (20) : ==> The requested action with this object has failed.:
$lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
$lastColvalue = ^ ERROR
->16:41:05 AutoIt3.exe ended.rc:1
+>16:41:05 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 13.28
 

[water]

The snippet of your script and the error message do not fit together

Can you please post the latest version of your script? I'm sure you've made some modifications since May.

 

[antmar904]

6 hours ago, water said:

 

The snippet of your script and the error message do not fit together

Can you please post the latest version of your script? I'm sure you've made some modifications since May.

 

@water

 

expandcollapse popup

#include <Excel.au3>
Local $oAppl = _Excel_Open()
If @error Then
    ConsoleWrite("ERROR: " & @error)
EndIf
Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\LNTest.xlsx") ; your workbook here
If @error Then
    ConsoleWrite("ERROR: " & @error)
EndIf
Global Const $xlUp = -4162
Global Const $xlToLeft = -4159
With $oWorkbook.ActiveSheet
    $countRow = .Cells(.Rows.Count, "A").End($xlUp).row
EndWith
ConsoleWrite("There are: " & $countRow & " rows in your workbook")
;Exit
For $i = 1 To $countRow
    With $oWorkbook.ActiveSheet
        $lastColvalue = .Cells($i, .Columns.Count).End($xlToLeft).value
        ;MsgBox(0, "", $lastColvalue)
        $aLastComputer = StringSplit($lastColvalue, ",")
        $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
        ;MsgBox(0, "", $sLastComputer)
        ConsoleWrite("The Script about to write: " & $sLastComputer & " in: " & "C" & $i & @CRLF)
        ;_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "C"&$i, "", "")
        If IsArray ($aLastComputer) Then
            ;MsgBox("", "", "I am an array")
            $sLastComputer = $aLastComputer[UBound($aLastComputer) - 1]
            _Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $sLastComputer, "C" & $i, "", "")
        Else
            ConsoleWrite("Skiping row: " & $i & " because there is no Computer Name to Write")
            ;MsgBox("", "", "I am not an array")
        EndIf
        If @error Then
            ConsoleWrite(@error)
        EndIf
    EndWith
Next