# 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 = 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 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 on other sites

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

Edited by MrCheese

## Create an account

Register a new account

• ### Similar Content

• #### _Excel_RangeFind() and empty search

By MWIProd,

• 3 replies
• 266 views

By hennenzac,

• 4 replies
• 944 views
• #### Error when run as a admin, Error on a line of code that does not exist (Error: Variable must be of type "Object") - (Moved)

By Garcia,

• 4 replies
• 495 views
• #### Help with delete row from array if value exist in cell

By kemo1987,

• 4 replies
• 782 views

By JohnnyTries,

• 4 replies
• 676 views
×

• Wiki

• Back

• #### Beta

• Git
• FAQ
×
• Create New...