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

    Find a line in import and copy in other sheet.. (2000 sr 1)

    I have a wobbok with 4 sheet.
    the sheet DEPROC is refred to ANOM_DEPROC
    the sheet DETRANS is refred to ANOM_DETRANS

    For DEPROC

    During the import txt is possible if is founded the value in the string ID_PROCEDURA the first 2 character "DX" import the entire line but not copy in to sheet DEPROC but put in sheet ANOM_DEPROC (naturally add up the existing line...)

    For DETRANS

    During the import txt is possible if is founded the value in the string ID_PROCEDURA the 2 character "DX" import the entire line but not copy in to sheet DETRANS but put in sheet ANOM_DETRANS (naturally add up the existing line...)

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    See if the attached does what you want.

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Tks, "brother" and tks for work.
    But your project not work correctly...
    I attache xsl.
    In effect the macro write always the line in the same line (from a2)

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Sorry, I made a stupid mistake. Change the FirstFree function to

    Public Function FirstFree(Tabella, Colonna)
    FirstFree = Worksheets(Tabella).Range(Colonna & 65536).End(xlUp).Row + 1
    End Function

    (I had Column instead of Row) <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Note: in the workbook you attached, the DEPROC worksheet doesn't have a column COD, the other three do. If the structure of DEPROC and ANOM_DEPROC is different, the code will not work correctly.

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Hans, the code (if you permit, with a little me modifiy) WORK GREAT!!!!!

    (I had Column instead of Row)... no problem you can all!

    Only a little question but in that instruction:

    Public Function FirstFree(Tabella, Colonna)
    FirstFree = Worksheets(Tabella).Range(Colonna & 65536).End(xlUp).Row + 1
    End Function

    Tabeela is the sheet to be set for count or not? .. because the code work in all cases, Boh!?

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Tabella is the worksheet name, and Colonna the name of the column to check, for example

    Dim n As Long
    n = FirstFree("Foglio1", "E")

    to find the first available row in column E in the worksheet Foglio1.

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Hi Hans,
    Ok, but i dont have nobody TABELLA sheet in my .xls, and the code work in other case....?!

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    To be more precise: Tabella is an argument to the FirstFree function. When you call the function, you must supply either a string such as "Foglio1" or a string variable that holds a worksheet name.

    Example 1: supply a string value
    Dim n As Long
    n = FirstFree("DEPROC", "A")

    Example 2: supply a string variable
    Dim n As Long
    Dim strFoglio As String
    strFoglio = "DEPROC"
    ...
    n = FirstFree(strFoglio, "A")

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Hi, Hans....
    Now all is clear.
    Tks.

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Hans, first goodmorning, i have a dubt... in order to macro DETRANS_1
    (Attached here a xls and txt file)
    Ths macro work, but:
    1) Why into column G of sheet anom_detrans is present another description an not EVIDENZA ANOMALIE
    2) Why the instruction (here) not work for the line copied into sheet anom_detrans?:

    Range("L" & Cont), Worksheets("DB").Range(Worksheets("DB").Range("A2" ), _
    Worksheets("DB").Range("B65536").End(xlUp)), 2, False)

    3) But the controll dupes (here) work also for the line copied into sheert anom_detrans?:

    ID = Cod_Procedura & "-" & Data_Cont_Corrente & "-" & Sport_Corrente & _
    "-" & CDI_1 & "/" & CDI_2 & "/" & CDI_3
    Set Found_ID = Sheets("DETRANS").Columns("N:N").Find(ID, LookIn:=xlFormulas)
    If Found_ID Is Nothing Then

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Try the code in the attached file (modifications are in the lines between ' *****):

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Big...
    The same tecnique can be used also into DEPROC...?

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    The line with Find is already OK there, but you must modify the two lines with VLookup the same way.

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    clear...
    I have mofify the vlookup, and all work fine.!
    Tks "brother".

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

    Re: Find a line in import and copy in other sheet.. (2000 sr 1)

    Hi Hans my head is fired;-(
    ... With the same tecnique of last macro is possible to export, during the import of txt, all line with the simbol "*" refered in to sheet DB... I have a big problem because the filtre of tath declare the import not is a variable of txt but a result of VlookUp

    Example:
    After the VlookUp in F (from the sheet DETRANS) in G, if have the result of VlookUp the first character is "*" skip this line and with the same tecnique of old macro copy and ad up the line not in the sheet DETRAN but recopy in to sheet OU_DETRANS...

    Hope i am clear...

Page 1 of 2 12 LastLast

Posting Permissions

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