Posts by Philosophaie

    I am trying to put arr1 onto Sheet1 but it is giving me this error:


    Quote

    Sub or function not defined.


    In module:


    Code
    Function func(ByRef arr As Variant) As Variant
        Dim arr1(8) As Variant
        For k = 1 To 8
            arr1(k) = arr(7 - k)
        Next k
        Call printarr(arr1)
        func = arr1(8) & arr1(7) & arr1(6) & arr1(5) & arr1(4) & arr1(3) & arr1(2) & arr1(1)
    End Function


    In Sheet1:


    Code
    Sub printarr(ByRef arr As Variant)
        With Sheets("Sheet1")
            For k = 1 To Len(arr)
                .Cells(7, k + 3) = arr(k)
            Next k
        End With
    End Sub


    Code
    =func(D3:K3)


    'In cell 5,3

    I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.


    In Module1:

    Code
    Function func1(ByRef arrayA())
        Dim arrayB(8)
        For k = 0 To 7
            arrayB(k + 1) = arrayA(k) * 2
        Next k
        func1 = arrayB
    End Function


    In sheet1:


    Code
    =func1(D3:K3)


    The input is D3:K3, arrayA.


    How does the resultant arrayB get populated onto the spreadsheet?

    Re: WorkSheet_Change not updating


    You have change a cell not a whole row or something else.


    I have put in "Sheet1" in VBA it just:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox ("InsertOrDelete")
    End Sub


    and nothing happens when I right click on the row #s just to the right of columnA and Select "Insert".


    You try.

    When you delete or insert a row (right click the the row #s on the left before the first column):

    Sub WorkSheet_Change(Byval Target As Range)
    'code
    End Sub


    does not detect that insert or delete row so nothing changes and Access is not updated.


    Is there some way of detecting when the insert row or delete row has been activated to do something that will make WorkSheet_Change notice.

    In Excel VBA I would like to create a Recordset and have it delete all records in an Access table keeping the fields.


    In VBA Excel transferring data from Excel to Access.


    I want to know how to increment the [ID] for each value of the range. [ID] is the Primary Key.


    I have tried "NULL".


    I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."


    Code
    For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)      accDateSub = Cells(value.Row, 1)      accDescSub = Cells(value.Row, 2)      accSub = Cells(value.Row, 3)      thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("[U]What goes here?[/U] ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"      conn.Execute CommandText:=thisSQLNext

    Re: Troubleshoot a INSERT INTO statement in Excel


    I ran it with Debug.Print thisSQL. Got a "Object Required" error


    Here are my Fields:
    ID, DateSub, DescSub, Sub, RunningTotal, Add, DescAdd, and DateAdd.
    Eight in total.
    Could someone show me where my syntax is Wrong.


    Code
    thisSQL = "INSERT INTO [a Chase Table] ([ID], [DateSub], [DescSub], [Sub], [RunningTotal], [Add], [DescAdd], [DateAdd]) VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#, '" & accDescSub & "', " & accSub & ", " & accRunTotal & ", " & accAdd & ", '" & accDescAdd & "', #" & Format(accDateAdd, "yyyy/mm/dd") & "#);"


    Is it possible the syntax for the Dates are wrong?

    Everything is functioning correctly except to "INSERT INTO" statement. Could someone help troubleshoot it with me.