# 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

• Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
#include "wd_core.au3" #include "wd_helper.au3" Local \$sDesiredCapabilities, \$sSession, \$sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') \$sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() \$sSession = _WD_CreateSession(\$sDesiredCapabilities) _WD_Navigate(\$sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait(\$sSession) Sleep(3000) Local \$sElement = _WD_FindElement(\$sSession, \$_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction(\$sSession, \$sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
__WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; \$iResult = 0; \$sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance!

• By GOSM
Hey,
as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.

Local \$oExcel = _Excel_Open() If @error Then Exit MsgBox(\$MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste(\$oExcel.ActiveSheet, "V4:W43", "X4", False, \$xlPasteValues, Default, True) If @error Then Exit MsgBox(\$MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox(\$MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option \$xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
Thanks for the help Simon
Copy Values Test.zip
• By Chimp
An "improper" "unusual" use of Excel
the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
Although definitely useless ... I find it quite funny though
have a good time
many thanks to @UEZ , @Malkey , @water
p.s.
I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
p.p.s.
strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells(\$iHcells = 50) ; Check application object Local \$oExcel = _Excel_Open() If Not IsObj(\$oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local \$sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If \$sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook \$oWorkbook = _Excel_BookNew(\$oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local \$hBmp = _GDIPlus_BitmapCreateFromFile(\$sPath) Local \$iWidth = _GDIPlus_ImageGetWidth(\$hBmp) ; get image width ; Local \$iHeight = _GDIPlus_ImageGetHeight(\$hBmp) ; not needed here Local \$iStep = \$iWidth / \$iHcells ; calculate the pixelation factor Local \$hBitmap_new = _GDIPlus_PixelateBitmap(\$hBmp, \$iStep) ; pixelate the image Local \$aPixelColors = _FileImageToArray(\$hBitmap_new) ; get pixel colors ; reduce Excel columns width Local \$xx = 1, \$yy = 1 For \$iCol = 0 To UBound(\$aPixelColors, 2) - 1 Step \$iStep \$oWorkbook.Sheets(1).Columns(\$xx).ColumnWidth = 1 \$xx += 1 Next ; reduce Excel rows height For \$iRow = 0 To UBound(\$aPixelColors) - 1 Step \$iStep \$oWorkbook.Sheets(1).Rows(\$yy).RowHeight = 9 \$yy += 1 Next \$xx = 1 \$yy = 1 For \$iRow = 0 To UBound(\$aPixelColors) - 1 Step \$iStep For \$iCol = 0 To UBound(\$aPixelColors, 2) - 1 Step \$iStep \$oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter(\$xx) & \$yy).Interior.Color = Number("0x" & \$aPixelColors[\$iRow][\$iCol]) \$xx += 1 Next \$yy += 1 \$xx = 1 Next _GDIPlus_BitmapDispose(\$hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap(\$hBitmap, \$iPixelate, \$bSmooth = 1) Local \$iWidth = _GDIPlus_ImageGetWidth(\$hBitmap), \$iHeight = _GDIPlus_ImageGetHeight(\$hBitmap) Local \$iNewW = Round(\$iWidth / \$iPixelate, 0), \$iNewH = Round(\$iHeight / \$iPixelate, 0) Local \$hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0(\$iNewW, \$iNewH) Local \$hCtxt = _GDIPlus_ImageGetGraphicsContext(\$hBitmap_scaled) Local \$iInterpolation = 5 If \$bSmooth Then \$iInterpolation = \$GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode(\$hCtxt, \$iInterpolation) _GDIPlus_GraphicsDrawImageRect(\$hCtxt, \$hBitmap, 0, 0, \$iNewW, \$iNewH) _GDIPlus_GraphicsDispose(\$hCtxt) Local \$hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0(\$iWidth, \$iHeight) \$hCtxt = _GDIPlus_ImageGetGraphicsContext(\$hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode(\$hCtxt, \$GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect(\$hCtxt, \$hBitmap_scaled, 0, 0, \$iNewW, \$iNewH, -\$iPixelate, -\$iPixelate, \$iWidth + 2 * \$iPixelate, \$iHeight + 2 * \$iPixelate) _GDIPlus_GraphicsDispose(\$hCtxt) Return \$hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray(\$hImage) Local \$Reslt, \$stride, \$format, \$Scan0, \$iIW, \$iIH ; , \$hImage Local \$v_Buffer, \$width, \$height ; _GDIPlus_Startup() ; \$hImage = _GDIPlus_ImageLoadFromFile(\$sFileName) \$iIW = _GDIPlus_ImageGetWidth(\$hImage) \$iIH = _GDIPlus_ImageGetHeight(\$hImage) ProgressOn("Progress Bar", "Filling a " & \$iIW & " x " & \$iIH & " size array.", "0 percent") \$Reslt = _GDIPlus_BitmapLockBits(\$hImage, 0, 0, \$iIW, \$iIH, \$GDIP_ILMREAD, \$GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () \$width = DllStructGetData(\$Reslt, "width") \$height = DllStructGetData(\$Reslt, "height") \$stride = DllStructGetData(\$Reslt, "stride") \$format = DllStructGetData(\$Reslt, "format") \$Scan0 = DllStructGetData(\$Reslt, "Scan0") Local \$aArray[\$height][\$width] For \$j = 0 To \$iIH - 1 For \$i = 0 To \$iIW - 1 \$v_Buffer = DllStructCreate("dword", \$Scan0 + (\$j * \$stride) + (\$i * 4)) \$aArray[\$j][\$i] = StringRegExpReplace(Hex(DllStructGetData(\$v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * \$j / (\$iIH)), Int(100 * \$j / (\$iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits(\$hImage, \$Reslt) ProgressOff() _GDIPlus_ImageDispose(\$hImage) Return \$aArray EndFunc ;==>_FileImageToArray
• By Zaoka
Hi guys
Need little help with filtering.
I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :

#include <Excel.au3> Global \$oExcel = _Excel_Open() Global \$oWorkbook = _Excel_BookOpen(\$oExcel, "C:\Users\....\Orders.xlsb") \$oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
error: Array(): undefined function. Not sure how to resolve this.
• By Rajat231
I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
SavingWorksheets.au3
×

• Wiki

• Back

• #### Beta

• Git
• FAQ
• Our Picks
×
• Create New...