Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    why the macro copy not want i want???? (2000 sr-1)

    I use this macro to match value in range H:S of RETAIL_POE and column H of GAF and copy all data filtred in TEMPLATE, but the code copy in TEMPLATE also all lines with blank cell in H of GAF and all lines not in range H:S of RETAIL_POE...

    Sorry for dimension of file out only 42k
    http://www.mytempdir.com/975876

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 sr-1)

    Your workbook doesn't contain macros.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the macro copy not want i want???? (2000 s

    i am stupid!
    Sub MERCATO_POE()
    Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
    Application.ScreenUpdating = False
    Sheets("TEMPLATE").Range("A2:M5000").ClearContents
    For v = 2 To Sheets("RETAIL_POE").Range("f" & Rows.Count).End(xlUp).Row
    With Sheets("GAF").Columns("h")
    Set c = .Find(Sheets("RETAIL_POE").Cells(v, "h").Value, , , xlWhole)
    If Not c Is Nothing Then
    f = c.Address
    Do
    Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -3).Resize(, 2).Value
    Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Resize(, 8).Value
    Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 10).Resize(, 3).Value = c.Offset(, 10).Resize(, 3).Value
    Set c = .FindNextŠ
    Loop Until f = c.Address
    End If

    End With
    Next
    Application.ScreenUpdating = False
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 s

    Your macro loops through cells in column H on RETAIL_POE, but all cells in H2:H45 contain the same value 75, so the code will copy the same cells from GAF over and over again.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the macro copy not want i want???? (2000 s

    but my really prob is ...

    Copy in TEMPLATE all line from GAF if in column F of RETAIL_POE is present 75 76 77 78 79 80 81 82 83 84 95 96

    Example:

    In column F of RETAIL_POE is present 4500, copy in TEMPLATE all lines from GAF where in column D of GAF is present 4500 and in column H GAF is present 75 76 77 78 79 80 81 82 83 84 95 96...
    In column F of RETAIL_POE is present 4501, copy in TEMPLATE all lines from GAF where in column D of GAF is present 4501 and in column H GAF is present 75 76 77 78 79 80 81 82 83 84 95 96...

    i have posted a sheet RETAIL_POE with the same range H:S have the same value 75 76 77 78 79 80 81 82 83 84 95 96, but in official work not all code in F of RETAIL_POE it can have this range...

    for example 4552 it can have only 77 78 79 80 81 in this case the range is H:L

    ecc...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 s

    That is different from what you originally asked. It looks as if you're repeating the question from <post:=604,451>post 604,451</post:> now... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the macro copy not want i want???? (2000 s

    OK....Clear all.
    http://www.mytempdir.com/976689

    Example:
    The joint of column F and H in RETAIL_POE is 4532&75 scann in D and H of GAF and if the condition existis ok, copy in TEMPLATE the value this range:
    Worksheets("TEMPLATE").Range("A" & RIGA) = Worksheets("GAF").Range("A" & RIGA).Value
    Worksheets("TEMPLATE").Range("H" & RIGA) = Worksheets("GAF").Range("H" & RIGA).Value


    The joint of column F and H in RETAIL_POE is 4532&76 scann in D and H of GAF and if the condition existis ok, copy in TEMPLATE the value this range:

    Worksheets("TEMPLATE").Range("A" & RIGA) = Worksheets("GAF").Range("A" & RIGA).Value
    Worksheets("TEMPLATE").Range("H" & RIGA) = Worksheets("GAF").Range("H" & RIGA).Value

    continue the joint and the find do until in range of line 2 of RETAIL_POE is present blank cell in this case after Q, R is blank...

    Goto the next line in RETAIL_POE...

    The joint of column F and H in RETAIL_POE is 4534&75 scann in D and H of GAF and if the condition existis ok, copy in TEMPLATE the value this range:
    Worksheets("TEMPLATE").Range("A" & RIGA) = Worksheets("GAF").Range("A" & RIGA).Value
    Worksheets("TEMPLATE").Range("H" & RIGA) = Worksheets("GAF").Range("H" & RIGA).Value

    The joint of column F and H in RETAIL_POE is 4534&76 scann in D and H of GAF and if the condition existis ok, copy in TEMPLATE the value this range:

    Worksheets("TEMPLATE").Range("A" & RIGA) = Worksheets("GAF").Range("A" & RIGA).Value
    Worksheets("TEMPLATE").Range("H" & RIGA) = Worksheets("GAF").Range("H" & RIGA).Value

    ontinue the joint and the find do until in range of line 2 of RETAIL_POE is present blank cell in this case after S, t is blank...

    Goto the next line in RETAIL_POE...

    ecc...

    Note: i have used RIGA to count but i dont know wath you use in this case...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 s

    Sal,

    You are asking too much. If you cannot solve this problem yourself with all the help you have received from this and other forums, you should hire a professional programmer to do it for you.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the macro copy not want i want???? (2000 s

    Hi Hans and welcome in my home...
    I can post a macro for modify?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 s

    Sal, you can always ask a question. It is up to me (or whoever reads your post) to decide whether it is a reasonable question. If it is, I'll gladly answer.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the macro copy not want i want???? (2000 s

    ok tks...
    based GAF and RETAIL_POE...

    is posisble to arrange this macro to use a double filter from column F and all value in range H:S of RETAIL_POE... and column D and H of GAF...
    Example:
    the joint F2&H2 of RETAIL_POE is the same in D:H of GAF copy in TEMPALTE, the joint F2&H3 of RETAIL_POE is the same in D:H of GAF copy in TEMPALTE, ecc.. use this for line in RETAIL_POE.

    Sub MATCH_PER_PRIMO_LIVELLO_POE()

    Dim I, II, III As Long
    Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
    Application.ScreenUpdating = False
    Sheets("TEMPLATE").Range("A2:M5000").ClearContents
    For I = 2 To Sheets("RETAIL_POE").Range("H" & Rows.COUNT).End(xlUp).Row
    II = Sheets("RETAIL_POE").Cells(I, "H").End(xlToRight).Column
    For III = Sheets("RETAIL_POE").Cells(I, "H").Column To II
    With Sheets("GAF").Columns("H")
    Set C = .Find(Sheets("RETAIL_POE").Cells(I, III).Value, , , xlWhole)
    If Not C Is Nothing Then
    F = C.Address

    Do
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(1).Resize(, 2).Value = C.Offset(, -7).Resize(, 2).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 2).Resize(, 8).Value = C.Offset(, -4).Resize(, 8).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 10).Resize(, 3).Value = C.Offset(, 6).Resize(, 3).Value


    Set C = .FindNextŠ

    Loop Until F = C.Address
    End If

    End With
    Next
    Next
    Application.ScreenUpdating = False
    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: why the macro copy not want i want???? (2000 s

    Sal,

    You keep on asking the same question. I have told you several times now that it is too complicated.

    If you have a really new question, you're welcome to post it, but if you repeat the same old question, my answer will be the same.

Posting Permissions

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