SorryButImaNewbie

[SOLVED] How to code: Next alphabetic letter?

5 posts in this topic

#1 ·  Posted (edited)

Hello everyone!

I try to insert a column with _excel_rangeinsert. I loop over a specified row, with different strings, and I want to insert a colum after the "OSSZEG" string (the columletter for OSSZEG is stored in the $OSSZEGoszlop variable)

And here I have little logical problem.

I know that this string is in column, for example, C, but is there a way to tell the UDF to insert to $vRange.

Like:

_Excel_RangeInsert($ExcelObject.Activesheet, $OSSZEGoszlop &":"&"D")

Here I would like my script to be clever enough to know that If $OSSZEGoszlop is C the it insert between C and D, if its D its insert between D and E.

So My basic question: Is there a String++ thing in autoit I can use for this? (If $A = "A" , $A++ = "B" or something like this)

Alternatively I can only think of writing a  /Select Case/ for every letter and call me crazylazy but, I just don't think it is the best solution, also a that long case select would run like 2-4 seconds, which is kind of long for a column insert. (or It runs less then 0.5 sec and I'm just not very good at telling how long a script needs to be executed :) )

Thanks for the help!

Edited by SorryButImaNewbie

Share this post


Link to post
Share on other sites



#3 ·  Posted

if you have sheets with max range A...Z then you can it do this way: 

$OSSZEGoszlop='C'
$sRange=$OSSZEGoszlop&':'&Chr(Asc($OSSZEGoszlop)+1)
MsgBox(64,'Range',$sRange)

$sRange is what you are searching for.

1 person likes this

Share this post


Link to post
Share on other sites

#4 ·  Posted

Sure, also I dont really think it will help, the entire thing is getting a bit long, but here is the "relevant" part I think:

Local $OSSZEGoszlop = ""
Select
    Case $AOszlop = "OSSZEG"
        $OSSZEGoszlop = "A"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $Boszlop = "OSSZEG"
        $OSSZEGoszlop = "B"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $COszlop = "OSSZEG"
        $OSSZEGoszlop = "C"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $DOszlop = "OSSZEG"
        $OSSZEGoszlop = "D"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $EOszlop = "OSSZEG"
        $OSSZEGoszlop = "E"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $FOszlop = "OSSZEG"
        $OSSZEGoszlop = "F"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $GOszlop = "OSSZEG"
        $OSSZEGoszlop = "G"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $HOszlop = "OSSZEG"
        $OSSZEGoszlop = "H"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $IOszlop = "OSSZEG"
        $OSSZEGoszlop = "I"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $JOszlop = "OSSZEG"
        $OSSZEGoszlop = "J"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $KOszlop = "OSSZEG"
        $OSSZEGoszlop = "K"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $LOszlop = "OSSZEG"
        $OSSZEGoszlop = "L"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $MOszlop = "OSSZEG"
        $OSSZEGoszlop = "M"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $NOszlop = "OSSZEG"
        $OSSZEGoszlop = "N"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $OOszlop = "OSSZEG"
        $OSSZEGoszlop = "O"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $POszlop = "OSSZEG"
        $OSSZEGoszlop = "P"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $QOszlop = "OSSZEG"
        $OSSZEGoszlop = "Q"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $ROszlop = "OSSZEG"
        $OSSZEGoszlop = "R"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $SOszlop = "OSSZEG"
        $OSSZEGoszlop = "S"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $TOszlop = "OSSZEG"
        $OSSZEGoszlop = "T"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $UOszlop = "OSSZEG"
        $OSSZEGoszlop = "U"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $VOszlop = "OSSZEG"
        $OSSZEGoszlop = "V"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $WOszlop = "OSSZEG"
        $OSSZEGoszlop = "W"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $XOszlop = "OSSZEG"
        $OSSZEGoszlop = "X"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $YOszlop = "OSSZEG"
        $OSSZEGoszlop = "Y"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
    Case $ZOszlop = "OSSZEG"
        $OSSZEGoszlop = "Z"
        ;MsgBox($MB_SYSTEMMODAL, "Értesítés!", "A SZAMLATELJ : " & $SZAMLATELJoszlop  & " oszlop!")
EndSelect

MsgBox($MB_SYSTEMMODAL, " Értesítés", "Az OSSZEG oszlop a: " & $OSSZEGoszlop & "")
_Excel_RangeInsert($ExcelObject.Activesheet, $OSSZEGoszlop)
MsgBox($MB_SYSTEMMODAL, " Értesítés", "Elvben beszúrtam")

The $ExcelObject is the excel file I'm working on,

The slect case loop throught a specified row (the title row which I searched for above the code, giving me the $Aoszlop and the rest as variable, these variabels point to their respective column + a $CellNumb.

I read the title row, to find the string "OSSZEG", if I found it I would like the last row to insert a colum after the $OSSZEGoszlop, Thats why I would need the next letter in the alphabet (or possible AA or something, but I haven't seen any excel among these kind od documents, that used the AA, AB etc columns)

Share this post


Link to post
Share on other sites

#5 ·  Posted

15 minutes ago, AutoBert said:

if you have sheets with max range A...Z then you can it do this way: 

$OSSZEGoszlop='C'
$sRange=$OSSZEGoszlop&':'&Chr(Asc($OSSZEGoszlop)+1)
MsgBox(64,'Range',$sRange)

$sRange is what you are searching for.

Thanks AutoBert! and Thanks for ASCII :) Thats what I was looking for, I can modify it to fit my needs perfectly

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

  • Similar Content

    • SorryButImaNewbie
      ExcelUDF _RangeInsert (SOLVED)
      By 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
    • JohnOne
      Excel Inserting Row(s)
      By JohnOne
      Needing to Insert some rows into an excel doc.
      Went to the help file and found _Excel_RangeInsert and Example 3
      The example works fine, but when I try to modify it, to insert 2 rows before row 2 in active worksheet, it only inserts 1 row.
      Can anyone see what I'm doing wrong.
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oAppl = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\Extras\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeInsert Example", "Error opening workbook '" & @ScriptDir & "\Extra\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf Sleep(1000) ; ***************************************************************************** ; Insert 2 rows before row 2 on Active sheet ; ***************************************************************************** _Excel_RangeInsert($oWorkbook.ActiveSheet, "2:2") ; "Before row, number of rows to insert " 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.") Actual goal is to insert rows at end of sheet, but if I cannot understand this I've no chance of other.
      EDIT:
      If I change the money line to...
      _Excel_RangeInsert($oWorkbook.ActiveSheet, "2:3") Then it inserts 2 rows instead of 3.
      EDIT2:
      I think I've established that those numbers ("2:2") I thought meant "Before row number, number of rows to insert" do not mean that at all, after trying to insert rows at end with this...
      $insert = String($oWorkbook.ActiveSheet.UsedRange.Rows.Count + 1) & ":2" _Excel_RangeInsert($oWorkbook.ActiveSheet, $insert) Which inserted 2000 rows into the sheet.   I know one thing for sure... I'm doing it wrong.   EDIT3:   Solution:   Problem was, I was using the completely wrong function (_Excel_RangeInsert) Where I should have been using _Excel_RangeWrite.