+ Reply to Thread
Results 1 to 7 of 7

Worksheet Change to loop & copypaste

  1. #1
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Worksheet Change to loop & copypaste

    Hi

    I am looking for help with a stuck code that i'm not capable to complete writing..


    I have an Activesheet named "Register" and I'm just trying to loop through a continuously populated range GF9:GF10000 in the sheet looking for numbers that are not zero, then copy all those non-zero values and PasteSpecial (only values no format) every one to each of its corresponding adjacent cell in the column GG9:GG10000. Each time I open the workbook the cell GG7 will calculate & produce a value that is either 0 (if nothing had changed) or negative or positive if some data had updated. The worksheet change target is GG7.

    Please Login or Register  to view this content.

    cheers,
    Stewart
    Last edited by MannStewart; 10-20-2020 at 10:35 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Worksheet Change to loop & copypaste

    Dim i As Long, Total As Long
    For i = 9 To LastRow
    If Register.Cells(i, "GF").Value <>0 Then
    ' Copy nonzero data in column GF & pastespecial to corresponding same row cell in GG
    End If
    Next i
    In keeping with your original code.



    Please Login or Register  to view this content.
    If the formula is in "GG7" then a worksheet_change event would not work. Worksheet_Change event does not kick in with a formula.

  3. #3
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: Worksheet Change to loop & copypaste

    hi Dave

    Thanks for your suggest & i guess the only place i can put the code now is in Worksheet Activate instead.

    I forgot to mention that the filled range to lastrow is non-contiguous,& may have blanks in GF, so i tried inserting your line into this new attempt but it returned COMPILE ERROR: ELSE WITHOUT IF. I tried repositioning & removing the END IFs and IFs but to no avail, can you point me out where it's wrong (i need the GG to be assigned a zero if its adjacent non-contiguous GF happens to be a blank).

    And, i also ran into another issue that is if just 1 cell in GF column returned a #VALUE error because of input errors by user in precedent cells elsewhere, i will get the Excel error msg RUN-TIME ERROR '13': TYPE MISMATCH. What is causing above 2 issues?

    Please Login or Register  to view this content.
    Stewart
    Last edited by MannStewart; 10-21-2020 at 03:35 AM.

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Worksheet Change to loop & copypaste

    Try this,

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: Worksheet Change to loop & copypaste

    hi Dave

    i need the value of GG to leave as it was (leave pre-existing old value alone) if GF happens to return an error, instead of assigning "Error" to GG , if so how should this line you gave be rephrased:
    Please Login or Register  to view this content.

    Stewart

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Worksheet Change to loop & copypaste

    You don't have to do anything, just leave that line blank

  7. #7
    Forum Contributor
    Join Date
    10-08-2019
    Location
    Singapore
    MS-Off Ver
    2016
    Posts
    326

    Re: Worksheet Change to loop & copypaste

    HI DAVE

    ok i had that done.

    However after reopening the file, the linked workbook that was contributing the source data to this main workbook could not open and crashed. On individually opening the linked workbook independently, Excel offered to repair the file and reopen, then this message

    Removed Part: /xl/calcChain.xml part with XML error. (Calculation properties) A document must contain exactly one root element. Line 2, column 0.


    can you say what is the meaning of this and what caused the crash?

    Stewart
    Last edited by MannStewart; 10-23-2020 at 05:06 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Loop to CopyPaste and repeat iterations
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-01-2016, 10:45 AM
  2. Code to copypaste large data in same worksheet
    By caooi23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2015, 02:33 AM
  3. iscfmet loop for worksheet change event
    By adamhassaan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-29-2014, 03:04 PM
  4. [SOLVED] DropBox value triggering copypaste by "SEARCHING" for the correct cells to copypaste
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-03-2013, 09:41 AM
  5. [SOLVED] Sort Data based on Criteria and CopyPaste to another worksheet
    By HowieD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2013, 10:54 AM
  6. Can you write a loop for Worksheet change event?
    By brently in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2013, 11:33 AM
  7. Loop through worksheet and insert row after a change in a cells value
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-27-2011, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1