Posts by Bazman


    I have an array called prices:

    Each time the code loops I want to set prices1 to prices
    then prices2 to prices etc. (prices itself will be changed on each loop).

    The only way I can think to do this is to have an array of arrays (I don't even know if this is possible!!!

    But all i really need is to create and name a new array to pricesn on the nth loop and set it equal to prices.

    Does anyone know how to do this?


    I have added several control boxes to my spreadshhet and depending on the user choices I want to hide groups of these user inboxes.

    I searched the threads and one said that you could do this by going onto Format control: Properties and changing the Object positioning to be "move and size with cells"

    However for my drop down boxes this option is ghosted out.

    So does anyone know of any other solutions?

    Hi I have an add in which I wrote and I'm trying to use it in a new program I am writing.

    I tried calling it as shown below but it hasn't worked what am I doing wrong?

    The addin is in interploate5.xla

    I am using Windows 2002

    l = [interpolate5].[Module1].inter("usd", "linear", theEnd) ' + dhAddWorkDaysA(5, theEnd, Holidays)) ' must add * Disc)

    Sub ter()
    Dim x As Variant
    Dim dater As Date
    dater = Range("A412").Value

    x = Application.VLookup(dater, Sheets("data").Range("A400:D500"), 4, True)

    End Sub

    This is a much shorete piece of code but although it rund x is set equal to Error 2042 is there anyway round this?

    I know ite very odd, bu the cell is defintely fine.

    I even check ed historical 01-Julys' incase it was picking up on of them by mistake but these were fine too!

    It must be something else, this problem is driving me crazy :-(((((((

    Hi Dave,

    The code runs but when you look at the value in the array in the locals window or by holding the curser over the varray name the value in just error 2042 no message.

    Platts is open, but its read only but I don't think that should make a difference? I have already tried this on a spreadshhet and it worked fine its just the VBA implementation that has thrown up this problem.

    The only other thing is that the format of the date on the sheet is different.
    The date is definitely there but VBA is looking for "01/07/04". But on the sheet it is "01-Jul-04 Thur". But like I say it ran fine on the spreadsheet.

    When I try and use the following Vlookup function in VBA it runs:

    but it returns Error 2042???

    Prices(n) = Application.VLookup(theStart, _
     Workbooks("Platts.xls").Sheets("Data").Range("A6:BG500"), 4, False)

    The full code is shown below what am I doing wrong. You can find the Vllokup about 4/5 of the way down

    Dim Holidays as Variant

    Set Holidays = Workbooks("SETUP.XLS").Sheets("HOLIDAYS").Range(Cells(3, commset(commno, 11)), Cells(holies, commset(commno, 11)))

    Hi I am trying to use the above code to read in arrays from a worksheet set up, but when I try and run it, it comes up with Run-time error 9 subscript out of range.

    Bit if I use

    Set Holidays = Workbooks("SETUP.XLS").Sheets("HOLIDAYS").Range("B2:B143")

    It works fine, butI have to keep the Range flexible, how can I do this?


    I have some code that I am debugging.

    One of the arrays is a Public array so I can not see the contents of that array in my locals window is there some other way to see the contents of the array? Preferably in something similar to teh locals window


    I'm trying to link a spredsheet I am working on to a program in a spreadsheet I developed previously.

    when in the VBA editor I go to tools/references the list of availanble references does not include the worksheet I want to link to. Instead it just has a VBAProject whose location is that of the file I wish to link to.When I tick it and click OK it comes u with a warning saying:

    Name conflicts with existing, module, project or object library!!

    Any hints on how to get round this??

    its wierd I did as you suggested witht the breakpoint and for lower combinationd of sims* steps the program just stops as you would expect it to. But in higher combinations it gets to the end but seems to want to keep going. With still higher combinations it doesn't get to the end but does try to start over again!! Is it running out of memory?

    Like I say if I could just get teh program to stop when it gets the answer that would be perfect is there any command that explicitly tells it to stop calculating?


    In my program on each loop I fill an array with a set of random variables.

    I then want to use the MMult function to multiply this array with another matrix, but so far it does't seem to be working!!

    CAn I use arrays with the MMult function? and if not what should I be doing?

    hi guys,

    This problem seems similar to before but it simply will not work!!

    Cells(x + 2, 4).Value = "=if(" & Cells(x + 2, 3) & "<>"",vlookup(" & Cells(x + 2, 2) & ",USD!A5:J26,10,False),"")"

    if I try to run this it comes up with 'Run-time error 1004' application-defined or object-defined error

    Its really frestrating :mad: