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:

Private Sub Lithology_DropButtonClick()
     'Populate stain.
    Dim myValue As String
    Dim rngLithology As Range
    Dim ws As Worksheet
    Set ws = Worksheets("LookupLists")
    myValue = Me.Lithology.Value
    Me.Lithology.Clear
    For Each rngLithology In ws.Range("Lithology")
        Me.Lithology.AddItem rngLithology.Value
    Next rngLithology
    Me.Lithology.Value = myValue
End Sub

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

Here is the corrected code:

Private Sub Lithology_DropButtonClick() 
     'Populate stain.
     
    Dim rngLithology As Range 
    Dim ws As Worksheet 
    Set ws = Worksheets("LookupLists") 
    Me.Lithology.Clear
    For Each rngLithology In ws.Range("Lithology") 
        Me.Lithology.AddItem rngLithology.Value 
    Next rngLithology 
     
End Sub

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 http://www.techrepublic.com/bl…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?

Private Sub Lithology_DropButtonClick()
    'Populate stain.
    
    Dim rngLithology As Range
    Dim ws As Worksheet
    Set ws = Worksheets("LookupLists")
        For Each rngLithology In ws.Range("Lithology")
            Me.Lithology.AddItem rngLithology.Value
        Next rngLithology
    
End Sub

Re: VBA Delete rows that are not divisible by 50

Appreciate the suggestion cytop. Here is the final code:

Sub Button1_Click()
    Dim FrstRng As Range, Lrw As Long
    Dim UnionRng As Range
    Dim c As Range


    Lrw = Cells(Rows.Count, "A").End(xlUp).Row
    Set FrstRng = Range("A2:A" & Lrw)


    For Each c In FrstRng.Cells
        If Int(c / 50) / (c / 50) <> 1 Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
            Else
                Set UnionRng = c
            End If
        End If
    Next c


    UnionRng.EntireRow.Delete
End Sub

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.

ub Divis50()
'
'
Sheets("VERT SCALES").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
    "=IF((OR((RIGHT(RC[-2],2)=""50""),(RIGHT(RC[-2],2)=""00""))),""YES"",""NO"")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C6062")


'sort filtered results
Range("C2").Select  
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Add Key:=Range("C2") _
    , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VERT SCALES").Sort
    .SetRange Range("A2:C6062")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


' scroll to first no and delete rows
Rows("123:123").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


'sort "A" back to consecutive numbers
Range("A2").Select
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Add Key:=Range("A2") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VERT SCALES").Sort
    .SetRange Range("A2:C122")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With


'delete filtered column
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
End Sub

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

[SOLVED]. That worked. Thank you.

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:

ActiveChart.SetElement (msoElementPrimaryCategoryAxisShow)
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory).TickLabels.Font
        .Name = Arial
        .Size = 8
        .Bold = msoTrue
    End With


    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue).TickLabels.Font
        .Name = Arial
        .Size = 8
        .Bold = msoTrue
    End With

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

Thank you. The code runs but now the tick labels disappear from the graph. Excel says they are being displayed but I can't get them back, even by going into Layout.

Re: VBA finding end of array within function

forum.ozgrid.com/index.php?attachment/64206/

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:

Sub DepthSeries()
'
' finds maximum depth recorded and uses number to produce number series for spline array
'


'
    Sheet1.range("G1").FormulaR1C1 = "=MAX(C[-6])"
    range("G2").Select


    Call CSplineDepth1(Sheet1.range("G2"), "Depth", Sheet1.range("A3"), Sheet1.range("G1"), 1)


   
End Sub




Sub CSplineDepth1(StartCell As range, Header As String, FirstN As Integer, LastN As Integer, StepN As Integer)


' Integer version produces series to maximum depth for array interpolation


    Dim i As Integer ' Value
    Dim r As Integer ' row


    StartCell.Cells(1).Value = Header ' Cells(1) makes sure it only uses the first cell of passed-in range
                                        ' in case you pass in a multi-celled range for StartCell
    i = FirstN
    r = 1


    Application.ScreenUpdating = False ' Much faster, so the screen is not refreshed until all the values in place


    For i = FirstN To LastN Step StepN
        StartCell.Cells(1).Offset(r, 0).Value = i
        i = i
        r = r + 1
    Next i


    Application.ScreenUpdating = True
    
' Excel formula that produces array for DepthSeries
' Range("H3:H" & lastRow).FormulaArray = CSplineA($A$2:$A$578,B$2:B$578,$G$2:$G$12322)
' Range("I3:I" & lastRow).FormulaArray = CSplineA($A$2:$A$578,C$2:C$578,$G$2:$G$12322)
' Range("J3:J" & lastRow).FormulaArray = CSplineA($A$2:$A$578,D$2:D$578,$G$2:$G$12322)
' Range("K3:K" & lastRow).FormulaArray = CSplineA($A$2:$A$578,E$2:E$578,$G$2:$G$12322)
' below is vba code used to automate cspline function
    
 lastRow = range("A" & Rows.Count).End(xlUp).Row
 
    range("H3:H" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C2:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("I3:I" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C3:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("J3:J" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C4:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    range("K3:K" & lastRow).FormulaArray = "=csplinea(R3C1:R" & lastRow & "C1,R3C3:R" & lastRow & "C3,R3C7:R" & range("A" & lastRow) - range("A3") + 1 & "C7)"
    
End Sub ' CSplineDepth1

Re: VBA Macro to Interpolate Between Values

SOLVED. Somewhat...now I am working on the automation part. Thank you for you help on this one...and thanks for your help on the other post!

Re: VBA finding end of array within function

[ATTACH=CONFIG]64191[/ATTACH]

I believe the first sub routine somehow got "mangled" since I had to go back to my original worksheet, but the spline sub routine up to now is in there. The first subroutine is to find the maximum value in range "A" and use that as the end value for the spline sub routine.

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

Apologies Stephen. Can you link me to the forum rules area?

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.

range("H3:H308").Select
    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,
Zach

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:

Sub Macro50()
'
' Macro50 Macro
'


'
    ActiveSheet.ChartObjects("Chart 12").Activate
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial"
        .NameFarEast = "Arial"
        .Name = "Arial"
    End With
    ActiveChart.Axes(xlCategory).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial"
        .NameFarEast = "Arial"
        .Name = "Arial"
    End With
    ActiveChart.Axes(xlValue).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial"
        .NameFarEast = "Arial"
        .Name = "Arial"
    End With
    ActiveChart.Axes(xlCategory, xlSecondary).Select
    With Selection.Format.TextFrame2.TextRange.Font
        .NameComplexScript = "Arial"
        .NameFarEast = "Arial"
        .Name = "Arial"
    End With
    ActiveChart.Axes(xlCategory, xlSecondary).AxisTitle.Select
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoFalse
    ActiveChart.Axes(xlValue).AxisTitle.Select
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoFalse
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    Selection.Format.TextFrame2.TextRange.Font.Bold = msoFalse
    ActiveChart.plotarea.Select
    Selection.Top = 33.507
    Selection.Height = 442.982
    Selection.Width = 278.646
End Sub

Regards,
Zach

Re: VBA Macro to Interpolate Between Values

Quote from StephenR;738204

The attached has the formula for your data (in yellow), which has produced some results - have a look and let me know if they look right. The process could be automated.

https://www.dropbox.com/s/0rch…/cubic%20spline.xlsm?dl=0

That's exactly what I'm looking for. Thanks, Stephen. I'll let you know how it turns out!

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.