Posts by ekma234


    i'm trying to align grid lines of 3 separate Excel charts as seen on image (i'm new to charts), representing Actual sale by products and % variance to Plan/Prior Year.

    Manually i cannot align them to the exact alignment (check the last grid lines below):

    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Variance chart.PNG","data-attachmentid":1213981}[/ATTACH]
    Is it possible to align grid lines just by setting the correct dimensions of chart/plot area?

    Is there any better way to achieve the same result ? maybe by combining all 3 charts using a combo chart?

    I've used charts originally created & posted on

    Many thanks & best regards,

    Thank you Kenneth Hobson,
    i'm excited to tell you that the macro works excellent! :congrats:

    FYI, the error was 13.

    You are probably correct also with your statement:


    You might want to add a line to skip if the value ="". Otherwise, "" * 1 = 0.

    Before testing i have manually added a blank row to the file, just to test the behaviour.

    Additionally there are empty cells included in the sample source file (not part of manually added blank row), which represent a zero value, but cells do not include any value.

    After applying the macro, the following happens:
    - cells, part of blank rows, result with 0 values (change from blank to "0").
    - empty cells (not part of blank row) result as empty cells (no change).

    Is there a reason why Excel interprets some empty/blank cells as "" and some not ""?

    Changing the number format does not result in geting a zero value into a blank cell :(

    Thank you Carim,
    i've just tested and i get "Run-time error 13 Type mismatch" at the following line:

    ReDim Preserve myArr(1 To 8, 1 To R)

    I've also tried to select the specific range at "' Load Array with your Calculation" - is that necessary?

    Is there a reason why the C&R are switched in the following line:

    myArr(C, R) = .Cells(R, C) * 1



    i'm trying to create a VBA macro to convert an accounting general ledger exported as Text to Numbers.

    I found the only useful way at the link but the macro is written for 1-dimensional array only (1 column only)

    I'd like to use it for a table source (i.e. 18 rows & 8 columns), which means 2-dimensions. I've customised the code (pls. see below), but i get an error (see ERROR):

    I've checked the looping using below code, results in the Immediate window were fine - all cells included:

    Debug.Print "(" & i & "," & j & ")", var(i, j)

    I apologize, but i cannot provide an example file.

    Pls. help ;)