Jump to content
Sign in to follow this  
MrCheese

for next with excel range addition

Recommended Posts

Hi guys,

 

Wondering, is there a better way, likely to use 'for...next' to add a letter to each range, by moving right -> along a range of columns in excel.

I currently use this, but its clunky.

If $run = 1 Then $range = "B6:B41"
    If $run = 2 Then $range = "C6:C41"
    If $run = 3 Then $range = "D6:D41"
    If $run = 4 Then $range = "E6:E41"
    If $run = 5 Then $range = "F6:F41"
    If $run = 6 Then $range = "G6:G41"
    If $run = 7 Then $range = "H6:H41"
    If $run = 8 Then $range = "I6:I41"
    If $run = 9 Then $range = "J6:J41"
    If $run = 10 Then $range = "K6:K41"
    If $run = 11 Then $range = "L6:L41"
    If $run = 12 Then $range = "M6:M41"
    If $run = 13 Then $range = "N6:N41"
    If $run = 14 Then $range = "O6:O41"
    If $run = 15 Then $range = "P6:P41"
    If $run = 16 Then $range = "Q6:Q41"
    If $run = 17 Then $range = "R6:R41"
    If $run = 18 Then $range = "S6:S41"
    If $run = 19 Then $range = "T6:T41"
    If $run = 20 Then $range = "U6:U41"
    If $run = 21 Then $range = "V6:V41"
    If $run = 22 Then $range = "W6:W41"
    If $run = 23 Then $range = "X6:X41"
    If $run = 24 Then $range = "Y6:Y41"
    If $run = 25 Then $range = "Z6:Z41"
    If $run = 26 Then $range = "AA6:AA41"
    If $run = 27 Then $range = "AB6:AB41"
    If $run = 28 Then $range = "AC6:AC41"
    If $run = 29 Then $range = "AD6:AD41"
    If $run = 30 Then $range = "AE6:AE41"
    If $run = 31 Then $range = "AF6:AF41"
    If $run = 32 Then $range = "AG6:AG41"
    If $run = 33 Then $range = "AH6:AH41"
    If $run = 34 Then $range = "AI6:AI41"
    If $run = 35 Then $range = "AJ6:AJ41"
    If $run = 36 Then $range = "AK6:AK41"
    If $run = 37 Then $range = "AL6:AL41"
    If $run = 38 Then $range = "AM6:AM41"
    If $run = 39 Then $range = "AN6:AN41"
    If $run = 40 Then $range = "AO6:AO41"
    If $run = 41 Then $range = "AP6:AP41"
    If $run = 42 Then $range = "AQ6:AQ41"
    If $run = 43 Then $range = "AR6:AR41"
    If $run = 44 Then $range = "AS6:AS41"
    If $run = 45 Then $range = "AT6:AT41"
    If $run = 46 Then $range = "AU6:AU41"
    If $run = 47 Then $range = "AV6:AV41"
    If $run = 48 Then $range = "AW6:AW41"
    If $run = 49 Then $range = "AX6:AX41"
    If $run = 50 Then $range = "AY6:AY41"

 

Normally, if it was going down the rows, i'd use this:

 

For $i = 0 To UBound($iRowCount) - 1
    $row = $i + 1
    $range = "B"&$row&":B"&$row+1
Next

 

so something like this, but i don't know how to code sequential columns:

For $i = 0 To UBound($iColCount) - 1
    $col = $i + 1
    $range = $col&"1:"&$col&"40"
Next

 

If I don't make sense, let me know.

Any help would be great. thanks

Edited by MrCheese

Share this post


Link to post
Share on other sites

Look at the _Excel_ColumnToLetter and _Excel_ColumnToNumber functions.

Example:

#include <Excel.au3>

Local $vRange = _ExcelSelectRange(25, 6, 41)

MsgBox(0,'', $vRange)

Func _ExcelSelectRange($iColumn, $iRowStart, $iRowEnd)
    Local $sLetter = _Excel_ColumnToLetter($iColumn)
    Return $sLetter & $iRowStart & ":" & $sLetter & $iRowEnd
EndFunc

 

Edited by Subz

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By roeselpi
      hello again,
      it has been a long time since i have been here and a long time since i last used autoit. ever so often when the time allows me to, then i follow up on an idea that i had a long time ago. i have done all the work on paper but now it is up to writing it in autoit and i keep stumbling over many little issues here and there. sometimes after a few days i will try again and get a step further but sometimes it just will not help no matter how long i try and think about a solution. for most of you it will be the basics but for me it is not all that easy, but at least i give it a try.
      right, down to business:
      here is my code:
      #include <MsgBoxConstants.au3> #include <StringConstants.au3> #include <Array.au3> #include <String.au3> ; ; PART 1: define replacements and check with msgbox ; Global $y, $z $y = "Yes" $z = "No" MsgBox(0,"replacements", $y & @CRLF & $z) ;the replacements in a message box ; ; PART 2: set the texts and check via console and msgbox ; Global $my1string = "abab" ;the first specified text MsgBox(0,"my1string", $my1string) ;the message box to output the first specified text Global $my2string = "icic" ;the second specified text MsgBox(0,"my2string", $my2string) ;the message box to output the second specified text ; ; PART 3: transform the strings to individual arrays ; $my1array = StringSplit($my1string, "") $my1array[0] = "" _ArrayDelete($my1array, 0) _ArrayDisplay($my1array, "my1array") ;the display of the first specified array $my2array = StringSplit($my2string, "") $my2array[0] = "" _ArrayDelete($my2array, 0) _ArrayDisplay($my2array, "my2array") ;the display of the first specified array ; ; PART 4: create an empty array for filling ; Global $OutputArray[4] $OutputArray[0] = "" _ArrayDisplay($OutputArray, "OutputArray") ;the display of the first specified array ; ; PART 5: compare & fill empty OutputArray with data after evaluation ; Global $i, $j, $k For $i = 0 to UBound($my1array) -1 For $j = 0 to UBound($my2array) -1 For $k = 0 to UBound($OutputArray) -1 If $my1array[$i] = "a" And $my2array[$j] = "i" Then $OutputArray[$k] = $y Else $OutputArray[$k] = $z EndIf Next Next Next _ArrayDisplay($OutputArray, "OutputArray") ;the display of the Newly filled Array In "Part 2" i make a string that is converted to an array in "Part 3" ... Now, I know that "a" and "i" are always in the exact same spot in both arrays and so i wanted to compare this and make a further array to document my findings by saying "yes" or "no" ... however my new array keeps saying just "no" allthough i can clearly see and know that it should say:
      yes no yes no my guess is that there is something wrong within my for-loops and that the counting is somehow "off" i guess that when the first for-loop is finished it reaches the second whilst the second for-loop is checking the first which would explain why it always says "no" instead of seeing the obvious.
      so my question would be: what is wrong with my for-loop? or where am i making an error that ultimately gives me the wrong results?
      help is much appreciated.
      kind regards
      roeselpi
       
       
      PS: sorry for my not so great english spelling ... stupid german sitting here trying out intermediate english skills.
    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By Page2PagePro
      Excel VBA's IDE registers a Control-y as "cut this line of code".
      For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history.
      Though not perfect, I keep this tool running in background on startup.
      The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active.
      If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired).
       
      Here's the code:
      Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.
       
       
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
×
×
  • Create New...