ExcelUDF _RangeInsert (SOLVED)

[SorryButImaNewbie]

Hello esteem users of this forum!

I have a little problem with the _excel_rangeinsert.

I organize an array in the excel (like from A:8 to S:114) with rangesort. I make it ascending according to a code first, then by a country as follow:

_Excel_RangeSort($ExcelObject, Default, $ColumnOfArrayStart & $CellNumb & ":" & $EndColumnOfArray & $MelysegSzamlalo, $KNKODoszlop & ":" & $KNKODoszlop,$xlAscending,Default,$xlYes,Default,$xlSortColumns,$RENDTAGALLAMoszlop & ":" & $RENDTAGALLAMoszlop,$xlAscending)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Example 1", "Error sorting data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeSort Report", "Data successfully sorted in range" & $ColumnOfArrayStart & $CellNumb & ":" & $EndColumnOfArray & $MelysegSzamlalo)

where $KNKODoszlop is holding the latter of the ID code or somethingcolumn, while $RENDTAGALLAMoszlop is the country code columns latter (PL, UK etc.). This happens without a glitch.

Now I try to insert 2 new rows everywhere where a) the KNKOD changes, or b ) if the KNKOD the same but the country code is different.

To do this I played around with this code: (after failing first to do both I try to make it work with one for start)

Local $CellaOlvasoSzamlalo = $CellNumb + 1
;MsgBox($MB_SYSTEMMODAL, "Értesítés", "A $CellaOlvasoSzamlalo : " & $CellaOlvasoSzamlalo )
Do
    Local $KNKODOlvaso1 = _Excel_RangeRead($ExcelObject, Default, $KNKODoszlop & $CellaOlvasoSzamlalo)
    Local $KNKODOlvaso2 = _Excel_RangeRead($ExcelObject, Default, $KNKODoszlop & $CellaOlvasoSzamlalo + 1)
        If $KNKODOlvaso1 <> $KNKODOlvaso2 Then
            _Excel_RangeInsert($ExcelObject, $CellaOlvasoSzamlalo + 1 & ":2")
        EndIf

    $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1
Until $KNKODOlvaso2 = "" ;and $RENDTAGALLAMOlvaso2 = ""

So I have the $Cellnumb which contains the first row of the array im working with (the header, with titels like countrycode) from before this part of the code (Local, in the same function, I tried to cast it to Int, results are the same if I do, It should be int anyway) from it, I "calculate" "$CellaOlvasoSzamlalo" (sorry for Hungarian names ) Then use that to create a Do Until loop to check if the result of a cell is different from the cell beneth it in the KNKOD column, if its different, it should insert 2 rows between them. At least in my mind. After that it incrase the CellaOlvasoSzamlalo to go down to the next cell, and do this until the cell it reads is empty (and of the array).

 

I tried to play around with a few different ways, the best I could achive was that it inserted 9 rows to the 2. row. (there were 9 different KNKOD in my test excel). 

I'm not sure what mi missing, I think that the rangeinsert $Range is read as: "number1:number2" where number1 is the row where I want to insert while number2 is how many rows correct?

Anyway, I welcome any help or nudge of direction or insight if someone is willing to help.

Thank you, and have a nice life

Edited September 2, 2016 by SorryButImaNewbie
b ) was b)... it just looked a bit bad

[water]

1 hour ago, SorryButImaNewbie said:

I'm not sure what mi missing, I think that the rangeinsert $Range is read as: "number1:number2" where number1 is the row where I want to insert while number2 is how many rows correct?

_Excel_RangeInsert needs the first and last row, calculates the number of rows and inserts them before the first specified row.
Example "5:7" inserts 3 rows (5, 6, 7) before row 5.

[SorryButImaNewbie]

Thank you master water, I really start to owe you a beer (if you ever come to Budapest, you may get it )

My code is working now, well actually it stops after the first insert (since it gets empty cells), but I can handle that. Sorry for the long reaply time, I'm to tired to even finish this function, maybe tonight. anyway here is the code as it is now:

Do
    Local $KNKODOlvaso1 = _Excel_RangeRead($ExcelObject, Default, $KNKODoszlop & $CellaOlvasoSzamlalo)
    Local $KNKODOlvaso2 = _Excel_RangeRead($ExcelObject, Default, $KNKODoszlop & $CellaOlvasoSzamlalo + 1)
        If $KNKODOlvaso1 <> $KNKODOlvaso2 Then
            _Excel_RangeInsert($ExcelObject.Activesheet, $CellaOlvasoSzamlalo + 1 & ":" & $CellaOlvasoSzamlalo + 2)
            If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 3", "Error inserting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example 3", "Rows successfully inserted on worksheet 2.")
        EndIf
    $CellaOlvasoSzamlalo = $CellaOlvasoSzamlalo + 1
Until $KNKODOlvaso2 = "" ;and $RENDTAGALLAMOlvaso2 = ""

I also added Activesheet since @error gave back the value 1. I know its not the best solution but I will have time to make the program efficient and more user friendly once it does what I want it to do first

Thank you again

[water]

[water]