+ Reply to Thread
Results 1 to 13 of 13

Change code so it can continue save from last used row

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Change code so it can continue save from last used row

    HI I would like to make below code changed so when it save in Sheet "Match". it save from last used row. So i can save more than one result, without delete the preveous result.
    Please have a look thanks

    Sincerely
    Abjac

    I guess its here it have to be changed

    With Sheets("Match").Range("A1").Resize(, 2)
        
            .CurrentRegion.ClearContents
            .Value = [{"Names.","Customer Numbers."}]
             
            .Offset(1).Resize(n).Value = a

    The complete code


    Option Explicit
    
    Sub FIndmatches()
        Dim a, e, s, i As Long, n As Long
        a = Sheets("Findsheet").Cells(1).CurrentRegion.Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = 2 To UBound(a, 1)
                Set .Item(a(i, 1)) = _
                CreateObject("Scripting.Dictionary")
                .Item(a(i, 1)).CompareMode = 1
            Next
            a = Sheets("Lookupsheet").Cells(1).CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If .exists(a(i, 1)) Then .Item(a(i, 1))(a(i, 2)) = Empty
            Next
            ReDim a(1 To UBound(a, 1), 1 To 2)
            For Each e In .Keys
                n = n + 1: a(n, 1) = e: a(n, 2) = "No Match"
                If .Item(e).Count > 0 Then
                    For Each s In .Item(e).Keys
                        a(n, 2) = s: n = n + 1
                    Next
                    n = n - 1
                End If
            Next
            
        End With
        With Sheets("Match").Range("A1").Resize(, 2)
        
            .CurrentRegion.ClearContents
            .Value = [{"Names.","Customer Numbers."}]
             
            .Offset(1).Resize(n).Value = a
           
        End With
        Call QuickCull
    End Sub

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Change code so it can continue save from last used row

    It's not easy code to understand, attach a sample file if you want a quick answer
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    Hi patel and thanks. Here is a sample. Also i dont know but i need to put text into sheet "Lookupsheet" Column A. Other wise it come with and error.
    But the main thing is to have that i can continue in sheet Match, So it donet delete preveous result. Please have a look thanks

    Sincerely
    Abjac
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Change code so it can continue save from last used row

    Abjac,
    This code is defiantly written by Jindon. It does not have, or refer to saving sheets code. It does compare(Matching between two sheets and display the result on the third sheet. I am not sure which bit of the code you are referring to.

  5. #5
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    HI AB33. Yes i think this is correct. Could not remember who have provided it. But its Jindon

    I refer to the third sheet ("Match"). When i save. It clear the contents. But i would like to have that i can try again with other data and then save these new data. So it just continue saving in the first blank row in this sheet. Sorry maybe i did explain it wrong. I think its this peace which have to be changed, when it save the result..
    See below

    Thanks
    Abjac

    With Sheets("Match").Range("A1").Resize(, 2)
        
            .CurrentRegion.ClearContents
            .Value = [{"Names.","Customer Numbers."}]
             
            .Offset(1).Resize(n).Value = a
           
        End With

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Change code so it can continue save from last used row

    With Sheets("Match").Range("A1").Resize(, 2)
        
            .CurrentRegion.ClearContents  ' Clear all existing data in that sheet
            .Value = [{"Names.","Customer Numbers."}]' Header for names, customers
             
            .Offset(1).Resize(n).Value = b ' writes back the array value of b in to sheet match
     End With
    If you were to update your existing other two sheets and run the macro, it has to clear the existing data first before it copies in the form of b.
    If you do not clear the existing data, the macro will over write them and likely to get a wrong data.
    Last edited by AB33; 03-18-2013 at 11:51 AM.

  7. #7
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    HI AB33 and thanks. I am not sure i undestand it correctly. What i would like is that. When i run the code. It put the result from comparing in Sheet "Match".

    I would like if i exsample run it again. With other data in the 2 other sheet. It will not delete the data in sheet"Match". But just continue with the new result in the first free row in Sheet "Match". Dont know if i explanained it correct.
    And i dont know if its possible also. But i think it should be.

    Sincerely

    Abjac

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Change code so it can continue save from last used row

    This will copies in the next row plus 1

    Option Explicit
    
    Sub FIndmatches()
        Dim a, e, s, i As Long, n As Long
        a = Sheets("Findsheet").Cells(1).CurrentRegion.Value
        With CreateObject("Scripting.Dictionary")
            .comparemode = 1
            For i = 2 To UBound(a, 1)
                Set .Item(a(i, 1)) = CreateObject("Scripting.Dictionary")
                .Item(a(i, 1)).comparemode = 1
            Next
            a = Sheets("Lookupsheet").Cells(1).CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If .exists(a(i, 1)) Then .Item(a(i, 1))(a(i, 2)) = Empty
            Next
            ReDim a(1 To UBound(a, 1), 1 To 2)
            For Each e In .Keys
                n = n + 1
                a(n, 1) = e
                a(n, 2) = "No Match"
                If .Item(e).Count > 0 Then
                    For Each s In .Item(e).Keys
                        a(n, 2) = s
                        n = n + 1
                    Next
                    n = n - 1
                End If
            Next
          End With
        With Sheets("Match").UsedRange
            '.CurrentRegion.ClearContents
         .Range("A1:B1") = Array("Names.", "Customer Numbers.")
           With .Offset(.Rows.Count + 1).Resize(n)
           .Value = a
          End With
        End With
    End Sub
    Last edited by AB33; 03-18-2013 at 07:48 PM.

  9. #9
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    HI AB33. I will not work. I mean the code is working, But it clear all in sheet "Match", and start from row 2. I would like if it find a match and its from row 1 to 50. Then if i run the code again, it shall continue, from row 51 and forward in sheet "Match".
    So it dont clear before it copy again.

    Then i can run different data and collect all in sheet "Match". And then save this sheet.
    So second time it copy to the first free row in Sheet "Match".

    Maybe i dont explain it good i dont know. But this is what i really need.

    Please have a look

    sincerely
    Abjac

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Change code so it can continue save from last used row

    Amended code no 8

  11. #11
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    Hi AB33. Your right it run perfect in this test sample. I have some error in my real workbook. But i dont think its related to this one.
    But perfect code, sorry its working correct and perfect. Thanks allot.
    Sincerely

    Abjac

  12. #12
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    Hi AB33. your code is working perfect in this sample here. But give an error in my real workbook. Is it possible to send this workbook to you private?so you can have a look?
    Sincerely
    Abjac

  13. #13
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Change code so it can continue save from last used row

    Hi AB33. Sorry I dont know what happen with my real workbook. Now its working haha sorry sorry it seems to be fine. Will test more and let you know if any problems.

    Sincerely
    Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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