Search:

Type: Posts; User: JackDanIce; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.27 seconds.

  1. Replies
    4
    Views
    26

    Re: VBA write data from Excel to Access

    Including the header row doesn't resolve (same error thrown up)

    I'm not sure who I'd run this from Access, do you mean to test/resolve just the SQL query? If so, I'm not sure how to write:
    ...
  2. Replies
    4
    Views
    26

    Re: VBA write data from Excel to Access

    I've tried but same error, making me guess it's syntax related. Currently the write query reads as:



    INSERT INTO tbl_Data_ORIG SELECT * FROM...
  3. Replies
    4
    Views
    26

    VBA write data from Excel to Access

    Hi,

    Due to large amounts of data, I'm working on a system with an Excel front-end and Access as data store.

    For proof of concept/testing, I've successfully written code to read data from a...
  4. Re: Clustered column chart - highlight specific column if condition met

    Thank you both, lots to review! And much appreciated.
    Toadstool correct, I got my ranges wrong, meant A1:L1 etc
    Jon Peltier, I think "No Border & Gap Width" may be best work around or visually...
  5. Re: Clustered column chart - highlight specific column if condition met

    Hi Toadstool, thanks for replying :)

    I think I can adapt this, my dates are horizontal rather than vertical (unfortunately it's not a normalised table). Assume I can, unless I reply back with more...
  6. Clustered column chart - highlight specific column if condition met

    Hi,

    I have:

    A time line from A1:A10 (months)
    Revenue data: B1:B10 (+ve values)
    Cost of Revenue: C1:C10 (+ve values)
    Difference: D1:D10 with formula =ABS(C1-B1)<1000

    I currently use A1:C10...
  7. Replies
    9
    Views
    236

    Re: Excel 365 Macros running slow

    Thank you Steve, I wasn't aware of this issue, I only recently upgraded to Office 365. For now it is a process that can be left to run, but anticipate future faster demands :(
  8. Replies
    9
    Views
    236

    Re: Excel 365 Macros running slow

    Hi Moshim,

    Just checked, no updates available.

    However, I do understand about minimising read/write actions to the spreadsheet will speed up the execution.


    I just tried your code and it's...
  9. Replies
    9
    Views
    236

    Re: Excel 365 Macros running slow

    The GRT_1 is a named range and always 37R x 39C and there are about 40 in total, however, I'll try the debug.print suggestion, thank you.

    I just paused it and it's been running for about 3 hours...
  10. Replies
    9
    Views
    236

    Re: Excel 365 Macros running slow

    Thank you for posting, it's not code optimisation though, it's the same code I had last week that ran much much faster.

    Now it's running very slowly and unclear why. Right now code is still...
  11. Replies
    9
    Views
    236

    Re: Excel 365 Macros running slow

    Additionally, file is saved to desktop, opened in app and autosave is disabled.

    Ran twice just now with run-times: 50 seconds, 1min 5 seconds

    Output is 41 rows x 3 columns, not a large range
  12. Replies
    7
    Views
    152

    Re: VBA Copy Non Contiguous Columns

    Try:

    Sub Copy

    Dim LR as Long
    LR = Application.Max(Cells(Rows.Count, 2).End(xlUp).Row, Cells(Rows.Count, 4).End(xlUp).Row)

    Range(Replace("B2:B@,D2:D@", "@", LR)).Copy...
  13. Replies
    9
    Views
    236

    Excel 365 Macros running slow

    Hi,

    Since yesterday, I've noticed a number of macros running slower than before. I was using Excel 2013 and about a month ago upgraded to Office.365

    I have a range A1:AK39 where

    A1 = Table...
  14. Thread: VBA Loop

    by JackDanIce
    Replies
    4
    Views
    84

    Re: VBA Loop

    You're welcome
  15. Thread: VBA Loop

    by JackDanIce
    Replies
    4
    Views
    84

    Re: VBA Loop

    Try:


    Sub m1()

    Dim LastRow As Long
    Dim x As Long
    Dim y As Long
    Dim z As Long: z = 1
  16. Re: Copy/Paste from one workbook to another 2 workbooks with range

    The VBA project is locked so can't determine what test33.xlsm is doing nor can interact with the code to see why it doesn't open files.
  17. Replies
    13
    Views
    376

    Re: copy based on NAMES instead of ID

    You're welcome, glad it's resolved
  18. Replies
    13
    Views
    376

    Re: copy based on NAMES instead of ID

    Not sure what you mean, but perhaps:


    r.Resize(, uBound(b)).value = b
    r.Offset(1).Resize(, uBound(a)).value = a

    r is the cell you are writing contents of array b to, then your code is...
  19. Re: Copy/Paste from one workbook to another 2 workbooks with range

    It works for me when I mock some test files up. Without your files, I can't determine the cause of error.
    [/COLOR]
  20. Re: Copy/Paste from one workbook to another 2 workbooks with range

    Is the destination sheet named Sheet1 in the workbook that is open?

    If this gives the same error, without your files difficult to diagnose:


    Private Sub CommandButton1_Click()

    ...
  21. Replies
    13
    Views
    376

    Re: copy based on NAMES instead of ID

    [Untested] Replace all of the code with below and try:


    Sub LTF()

    Dim sFolder As String
    Dim sFile As String

    With Sheets("copy").Cells(2, 11)
    If Len(.Value) > 0...
  22. Re: Copy/Paste from one workbook to another 2 workbooks with range

    You can't update a closed workbook, you have to open it, paste the values, then close it.

    Replace all of your code with below and try:

    Private Sub CommandButton1_Click()

    Dim...
  23. Re: Copy/Paste from one workbook to another 2 workbooks with range

    Is Book17 the active workbook?
    Is Book16 and Book18 both open when you try to run the macro?

    If yes to both, then try:


    Private Sub CommandButton1_Click()

    With...
  24. Replies
    13
    Views
    376

    Re: copy based on NAMES instead of ID

    Can you post your full code?
  25. Replies
    13
    Views
    376

    Re: copy based on NAMES instead of ID

    Try replacing in above code with:

    For Each cell In wbtarget.Sheets(1).Range("ID", "Name", "class", "div", "sub1", "sub2", "sub3", "sub4", "sub5")
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4