for next with excel range addition

[MrCheese]

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.

expandcollapse popup

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 February 26, 2018 by MrCheese

[Subz]

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 February 27, 2018 by Subz

[MrCheese]

awesome! thanks heaps! I didn't know they existed.

Edited February 27, 2018 by MrCheese