# 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

• By goku200
I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
Example of filename:
12345_v1.0_TEST Name [12345]_01.01.2022.html
12345 would be in one column
v1.0 would be in another column
TEST Name [12345] would be in another column
01.01.2022 would be in another column
.html would be in another column
Note: filenames always change each day.
Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter

• I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

#include <Excel.au3> #include <MsgBoxConstants.au3> Local \$oExcel = _Excel_Open() Local \$oWorkbook = _Excel_BookNew(\$oExcel) _Excel_RangeWrite(\$oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!\$A:\$B,2,0))","W2",False) If @error Then Exit MsgBox(\$MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox(\$MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved.

• I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong.
My goal is to auto fill some formulas that are next to a pivot table in columns A-C.
_Excel_RangeWrite(\$oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite(\$oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite(\$oNewWorkBook,Default,"100","F3") _Excel_RangeWrite(\$oNewWorkBook,Default,"=(C3-F3)","G3") \$oNewWorkbook.ActiveSheet.Range("D3:G3").Select With \$oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling.
Hoping someone can point me in the right direction.
• By Rskm
Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
Tmp.xls
• By mmoalem
Hi all - I have created a simple script that turn on cctv software (contacam)  when my phone does not ping back (when I'm out of range of home network)
#include <MsgBoxConstants.au3> \$iPing = 0 For \$i = 5 To 1 Step -1 ; this 5 loops cycle is just for testing - please ignnore - when complete it will be an endless loop Example() Sleep(120000) Next Func Example() ; Ping the phone. \$iPing = Ping("192.168.1.102") If \$iPing Then ; If a value greater than 0 was returned then turn off camera. _endCCTV() Else _startCCTV() ; If a value of 0 was returned then turn on camera. EndIf EndFunc ;==>Example Func _startCCTV() ShellExecute("C:\contacam capture\FJ Camera\CAMERA.bat", "on") Sleep(10000) ShellExecute("C:\contacam capture\FJ Camera\CAMERA_REC_SENSITIVITY.bat", "50") EndFunc Func _endCCTV() ShellExecute("C:\contacam capture\FJ Camera\CAMERA_REC_SENSITIVITY.bat", "0") Sleep(10000) ShellExecute("C:\contacam capture\FJ Camera\CAMERA.bat", "off") EndFunc

the problem I have is that in this format the script run the bat file that turn on the camera (or off) everytime the ping runs but obviously once the ping returns 0 and the camera starts I only want to run the bat on ping NOT 0 (turn off camera)
×

• Wiki

• Back

• #### Beta

• Git
• FAQ
×
• Create New...