Posts by zach973

    Re: Dynamic lookup list producing duplicates in VBA form dropdown box

    When a selection is made in the box, the selection was cleared from the field and wouldn't stay visible. Here is the corrected code:

    Re: Dynamic lookup list producing duplicates in VBA form dropdown box

    Here is the corrected code:

    Form consists of dropdown and combo boxes that reference a dynamic lookup list. When the macro runs, every time a user selects the drop down box, it produces duplicates within the box. If a user clicks off the box selection, and selects the same drop down box again, it produces duplicates of the duplicates and so forth. The more the user clicks the same box, the more duplicates it creates. I've taken the original code from Susan Harkins at techrepublic…excel-with-a-dynamic-list.

    I'm obviously missing something, but the tutorial made it seem it's super simple easy using combo boxes with dynamic lookup lists. I followed the directions to a T but there seems to be some incomplete coding given. Is there a line of code that can tell Excel to stop repopulating the drop down after the 1st click?

    Re: VBA Delete rows that are not divisible by 50

    Appreciate the suggestion cytop. Here is the final code:

    I have many worksheets with sequential linear xy data that vary in length. I am looking for vba automation to delete (rows up) all data with the exception of x data (A) that is divisible by 50

    My macro produces a helper column that flags the data appropriately through formula. The Autofilter selects the the flags and deletes. I would like to make this dynamic and be able to change the divisor to 100 or 200 through the code. Here's what my macro looks like.

    Re: Formating chart object (plot) in VBA using 2010 Excel macro

    I have tried your recommendation. The tick labels and the entire axis "disappear" and I can't get them back going through layout and enabling default axis so there is no font or axis to select. Excel shows them as enabled. Even disabling and enabling produces no element, but the graph does shift, so it's doing something - just not visually displaying the elements. It is run on an existing xy scatter graph with default axes and labels, but I wouldn't think that would conflict since it is such a simple command. I've deleted a legend in Excel graphs before and have had the same problem getting it back trying to enable it in layout. If the below code looks acceptable, I'll have to go to the original code that produced the graph and take a look at that, but like I say it's just a default setting and the only code written to alter the axis was what I did to the Format Axis (min, max, units, reverse order).

    The entire code for changing axis label fonts:

    Re: VBA finding end of array within function

    It still not continuing through to end of row. I don't know why Ozgrid won't allow me to upload the file with the array and cspline modules, so this attachment only has the macro code - i believe you have an earlier iteration with those modules which are needed. I've also included the macro coding below:

    Re: VBA finding end of array within function

    Thank you for the clarification. That works but I'm still having the problem of the array stopping at the end of the set and not interpolating to the end of the array (C3,R3C7:R" & lastrow &"). I coded the array with the last row command which then output an array of all zeros and stopped at the set end, not at the end of the array. Something I've never seen before is that it also kicks me back into VBA, but there's no error or bug statement.

    When I look at the function formula in Excel, it has the array matching the data set end. Could it be something to do with the original command stating that lastRow is dependent upon "A" which consequently is R579, the end of the array? The all zero thing also confuses me but I think that is because the array cannot have a lastrow command as it is in it's current definition. Without the lastrow command coded into the array function (that is C3,R3C7:R1354357), the interpolation works beautifully, it just doesn't carry through.

    Re: VBA finding end of array within function

    Thank you. I think I'm part way there. The array stops at R579 but R3:R13543 is the interpolated range of data generated from R3:R579. I can run the macro and generate the appropriate array interp (without the lastRow command and using the specific ranges), but since the interpolated range will always be dependent upon different data sets, so will always the array, so therefore must be produced from the interpolation of the last row of Column A. I guess I'm not sure how to hardcode the value in the spline function.

    lastRow = range("A" & Rows.Count).End(xlUp).Row
        range("H3:H" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C2:R579C3,R3C7:R13543C7)"
        range("I3:I" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C3:R579C3,R3C7:R13543C7)"
        range("J3:J" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C4:R579C4,R3C7:R13543C7)"
        range("K3:K" & lastRow).FormulaArray = "=csplinea(R3C1:R579C1,R3C5:R579C5,R3C7:R13543C7)"

    Re: VBA array #VALUE error from function and finding end of array within function

    Du-oh. Please disregard the first portion of my question. I found the error within the dataset (duplicate cell values). The code still applies to my second question.

    I created a macro within Excel with the below code, however I noticed that using the entire range gave me a #VALUE error. I re-recorded the macro and only used a range up to 10 which output the correct results. Dissecting the code, I narrowed down the exact number where the array stops working at R308. When I change the array to R309 and < the output is #VALUE for the output array. My data range is to R579. The interpolation is from a data set in ascending order. Seems arbitrary but my lack of VBA experience precedes me.

        Selection.FormulaArray = "=csplinea(R3C1:R308C1,R3C2:R308C2,R3C7:R308C7)"

    Once I can figure this out, is there a way to find the end (such as a lastRow) within the formula's arrays when outputting the final array? I have data sets that vary in length and would like the automation to work regardless of range.

    Much appreciated,

    I've used the macro recorder to format a plot area in a chart (e.g. fonts, font size, position) to test. To test the code, I rearranged the chart objects of the original chart and changed the font, etc. When I run the macro on the very same chart I recorded the macro on, I get a 'Run-time error "2147467259 (80004005)': Method 'TextFrame2' of object 'ChartFormat' failed". I'm not sure why the same code generated through the macro feature fails when I use the same code that the macro generated while recording my desired output. This error occurs even when I've kept it simple as just changing the font and size of the x axis.

    I'm really new to VBA, but am finding that using the macro recorder is helping me to understand the basics at the very least - just confusing when the very function used to generate a code fails to run it's own creation. I'm finding that formatting the plot area with VBA has been a little bit enigmatic for me, as it seems to take any code only arbitrarily (macro can change the font for everything, but won't change the axes fonts or sizes and won't alter plot dimensions without giving an error or not executing) - and I'm using strictly the macro recorder to generate each test on one single chart. The code as generated by the Excel 2010 macro recorder function follows:


    Re: VBA Macro to Interpolate Between Values

    Quote from StephenR;738155

    Yes of course, that makes sense. Did you have a look at the link? It has a workbook with a custom function in it which does the interpolation and looks to me to be right, but I'm not sure what I'm looking at.

    Or can try shooteGUB1's solution.

    Thanks. I'll take a better look at that today. I've tried to implement shooteGUB1's solution but was wasn't having much success. I'm afraid it's probably due to more my lack of background with VB than the effectiveness of the code.

    Re: VBA Macro to Interpolate Between Values

    I believe that you need at least 3 if not 4 data points minimum for the CS. By only processing 2 points, you're correct in that you will get the same result as the LI due to the fact that there is only an origin and endpoint in which the interpolation is linear by default. From what I understand about CS is that it takes into account the preceding and next point between each given point to calculate the interpolation between each point.