Posts by eureka18

    got it to work:

    changed the loop, especially .setcredentials:

    Hi all,

    I'm creating some vba to automatically download files from a password protected webserver. The issue is that it's not working for some reason. The script does not error out and it also creates files. but if i open the file it downloaded it says:


    >This server could not verify that you
    are authorized to access the document
    requested. Either you supplied the wrong
    credentials (e.g., bad password), or your
    browser doesn't understand how to supply
    the credentials required.

    I'm sure that the credentials I put in are correct but for some reason it does not connect properly to the website. (if you try to access the website using a browser you get a popup to fill in login + pass). What am I misunderstanding on how to login on websites?[/QUOTE]

    Hi, I'm working on a document template made by someone else but I'm getting a weird issue I do not understand.

    Basically this person made a footer which is filled with information on document version (draft, finalised,etc) and the version number (version 1.0.1, etc). This data is sourced from a table within the document. The issue is however, that this footer is only filled for the first 2 pages of the document. After that it does not show. If I check in the footer than I do see that the field code is used but it just does not show a value. Tried refreshing and all. The last pasge where it does show is the page on which the table with the value is sourced.

    Does anyone understand why this is happening and how to solve this?

    {STYLEREF "Doc Status" \* MERGEFORMAT} • {STYLEREF "Doc Label Version" \* MERGEFORMAT}{STYLEREF "Doc Version" \* MERGEFORMAT}

    I also don't understand where/how the source of the Styleref is defined. I know I can find the style ref under Quick Parts -> Fields but it does not show where it get's the actual version of the document.

    That seems to work perfectly thanks!

    BELOW: Never mind, found the solution, I'm an idiot and forgot to redim the array.

    Now I have one other question regarding arrays. Don't know if it should be in this topic, Please let me know if I need to move the question to a new topic.

    So I multiplied the 2 arrays into ThroughputIn:

    For x = LBound(ThroughputOut) To UBound(ThroughputOut)
            For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
                For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                    ThroughputOut(x)(a, b) = ThroughputOut(x)(a, b) / 100 * DailyOutflow(a, b)
                Next b
            Next a
        Next x

    I now want to sum the rows within each sub array. For this I use the following code:

    I am getting a subscript out of range error in the public function on

    strArray(a) = Application.Sum(Application.Index(Arr(x), a))

    the weird thing is that if I use the immediate window to see

    Application.Sum(Application.Index(Arr(x), a))

    it gives me 1 value. But for some reason it will not be put into the



    I mean a basic loop construction like this:

    Dim x as long, y as long
    for x = lbound(array1) to ubound(array1)
    for y = lbound(array1, 2) to ubound(array1, 2)
    array1(x, y) = array1(x, y) * array2(x, y)
    next y
    next x

    I tried the following:

    For x = LBound(ThroughputOut) To UBound(ThroughputOut)
            For a = LBound(ThroughputOut(x), 1) To UBound(ThroughputOut(x), 1)
                For b = LBound(ThroughputOut(x), 2) To UBound(ThroughputOut(x), 2)
                    ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)
                Next b
            Next a
        Next x

    but i get a type mismatch error on

    ThroughputOut(x(a, b)) = ThroughputOut(x(a, b)) * DailyOutflow(a, b)

    I'm using the 2x2 as an an example. Real range is variable (depending on amount of days in month).

    Example is 30x58 for one range. The array will contain 24 of such ranges.

    Those 24 ranges should be multiplied with one other range which has the same dimensions (30x58)

    Other than seemingly ruling out matrix multiplication, that doesn't really clarify to be honest. Is ThroughputOut(0) supposed to be a 4 element array? If so, should it be 2*2 or some other alignment.

    The elements should be 2x2 for both the Throughput array, as well as the range.

    array{2x2, 2x2, 2x2} * range(2x2) = array_after_multiplication{2x2, 2x2, 2x2}

    What are you expecting as the output? Actual matrix multiplication, or just multipying each element by the corresponding element in the other array?

    I would expect the outcome to be an array with 24 elements (0 to 23). every element of the array should have the multiplication of the cell values of the ranges.

    So if you would take the example I described earlier. i would expect:

    ThroughputOut(0) = throughput(0).A1 * range.A1, throughput(0).B1 * range.B1, throughput(0).A2 * range.A2, throughput(0).B2 * range.B2
    ThroughputOut(1) = throughput(1).A4 * range.A1, throughput(1).B4 * range.B1, throughput(1).A5 * range.A2, throughput(1).B5 * range.B2


    I am working on a workbook in which I have to do some rather complicated formulas with arrays and ranges, it slowed excel down to the point that calculations took over half an hour. Hence, I wanted to do the calculations in vba.

    I have an array, which I fill with ranges. so say:

    Throughput Out = {A1:B2 ; A4:B5; A7:B8}

    Now I want to multiply this with a range in another sheet (A1:B2). I'm getting a type mismatch when I try to multiply the ranges in the array with the range in the other sheet:

    ThroughputOut(x) = ThroughputOut(x) * DailyOutflow

    Below my full code:

    That did the trick? I just don't understand how that sumproduct function works? it sums 2 arrays, one in which only things >0 are, and the other one <=85. But then how does it come to that output? the sumproduct is still an array right?


    I want to set references to a workbook without directly opening it (i want to put the workbooks in an array and loop through it later in the code)

    I know can be used but that directly opens it... Is there a way in which you do not open the workbook but only set the variable? Below code gives "subscript out of range" on Set NPCS1

    The only way we can say for sure is if you post the workbook so we can see where you've gone wrong.

    One thing to check is make sure that B5 contains a numeric value and that it is not formatted as text or is blank.

    What do you mean with cell B5? You mean "<85"? 85 should just mean smaller than 85

    added an example of the file with formula in yellow cell on sheet named Example


    I'm working on a formula which has to count which values in a range are smaller than 85. However, it always returns 0 and I can't seem to find out why.

    =COUNTIF(INDEX(IFERROR(VALUE(OFFSET('[MR Data.xlsx]Throughput OUT'!$C$1:$BW$31,MATCH("Throughput at  "&J16&"  (time)",'[MR Data.xlsx]Throughput OUT'!$B:$B,0)+1,0)),0),,MATCH($B16,'[MR Data.xlsx]Throughput OUT'!$C$12:$BW$12,0)),"<85")

    The range that I get is:


    So there should be values which are smaller than 85. Why do I get 0 results?

    Hi people,

    I've been crunching my head for a while now on this issue i have. I have a formula with an array of numbers. From this array I want to get the least negative number (the negative number closest to 0). I am using the following formula:


    For some reason excel gives me 0 as the outcome of this. What am I doing wrong?


    I have a userform which opens when the workbook is opened.

    Private Sub Workbook_Open()
    End Sub

    However, when I open the workbook, it also opens the VBA editor in front of the userform and it highlights in yellow. Only after I minimise the vba editor, I can see the userform. How can I disable the VBA editor from popping up?



    Public ImportWB As Workbook


    Set ImportWB = Workbooks.Open(StrPath & "\" & Item)

    I found out that the following code works

    ImportWB.Sheets(YCSht).Range(Cells(y, ExcelYCPlanningColumn), Cells(y, wColNum(ExcelYCPlanningColumn) + Horizon)).Copy
    ThisWorkbook.Sheets(YCSht).Cells(x, ExcelYCPlanningColumn).PasteSpecial xlPasteValues

    However, this should be possible in 1 row right?