Jump to content
LoneWolf_2106

Chart Alignment - Excel - Dynamic Range

Recommended Posts

LoneWolf_2106

Hi everybody,

i have a question about Excel, i have to create several charts one below the other dynamically.

I have thought to use:

 

$oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell)
$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count

 

And then to use it in this way:

$Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24

But it doesn't work with charts.

Does anyone have a suggestion?

 

Share this post


Link to post
Share on other sites
water

You could have a look at the ExcelChart UDF. Function _XLChart_ChartCreate allows to specify the cell range to use for a chart.
For download please check my signature.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

  • Similar Content

    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework:
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> HotKeySet("!v", "Pastedata") func Makearray() local $bArray() 'User has cells already copied 'Convert clipboard into an array 'I don't know how excel stores data to clipboard so don't know how to bring it into array _Arraydisplay($bArray) return $bArray end func func Pastedata() Local aArray 'make array based on assumption user has already copied a range to clipboard aArray = Makearray() 'paste code 'don't worry about this I got the rest end func  
    • nooneclose
      By nooneclose
      Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 
      Here is my code: 
      Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2 For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice      For $iN2 = 0 To $IndexRows Step 1          if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then              Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)              ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)              Local $eI  = $timeSheetName + 1              ;ConsoleWrite($timeSheetName & @CRLF)              ;ConsoleWrite($eI & @CRLF)              ;ConsoleWrite(@CRLF)              _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)                                                                          ;Inserts a empty cell in columns E and F.              _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)                                                                         ;Fills the empty cell in columns E with the doubled name              $aArray_Index = 2                                           ;Array element counter              For $Index = 2 To $IndexRows Step 1                        ;Loops through every row in the Excel file unto no rows are found or a null row is found                  $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)                  $e_names[$aArray_Index] = $Array_Value_E                ;While the code loops every value in column E is stored in the E array (updating the array)                  $aArray_Index += 1              Next              ExitLoop          EndIf      Next      $nI  += 1      $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
      I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 
      Any ideas on how to improve the "update" for my array?
      (once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
      Any and all tips would be greatly appreciated. 
       
      NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 
    • smud
      By smud
      Currently, I'm working on a program that will display Dialog boxes with either Yes or No.
      For each dialog, I reward the user with X amount of Credits.
      I'm hoping to output the amount of credits to a cell in a column (there will be 20 different columns).
      It will only post to a row that is equal to today's date (first column). If no row exists yet with the current date, it will start a new row.
      Any suggestions?
      Thank you
    • nooneclose
      By nooneclose
      How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.
       ActiveSheet.Outline.ShowLevels RowLevels:=2 I need this to close my subtotal once it is finished. 
      any help will be greatly appreciated. 
    • Skeletor
      By Skeletor
      Hi All,
      While creating a few excel spreadsheets using AutoIt, I came across something which to my limiting time to research the forums I don't anyone has mentioned. 
      The color pallettes are reversed. 
      Huge shock to me.
       
      I wanted to produce a red row but kept on getting blue. 
      Seems like 0xFF0000 was red on the charts but when running the script, I got blue. 
      I then played around with the colors, and after a few tries, I finally got Red. 
      Reversed the FF0000 and the result is 0000FF.
       
      So for Excel compared to Html
      0000FF (Red) - Excel
      0000FF (Blue) - Html

      FFFF00 (Cyan) - Excel
      FFFF00(Yellow) - Html
       
×