Jump to content

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
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
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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • 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
       
    • By SkysLastChance
      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. 
       
       
    • By SkysLastChance
      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) 
      any ideas/advice?
×
×
  • Create New...