Posts by Philosophaie

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


    Sub or function not defined.

    In module:

    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:

    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


    '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:

    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:


    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)
    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 & ",..."

    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.

    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.