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

    intercept the line (2000 sr 1)

    In this sheet is present in a column B6:B70 a code.
    My problem is to intercept this line from the variable AGENZIA and put the realted variable imported from a txt file.
    For example the variable is 00500 put the related value in the line identify with 00500
    The sheet22 are the final result....

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

    Re: intercept the line (2000 sr 1)

    I don't have the slightest idea what you are asking. Please try to explain again, if possible with a detailed example of what you want to accomplish. (There is no Sheet22 in your workbook.)

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

    Re: intercept the line (2000 sr 1)

    the macro in question is IMPORTA.
    The sheet Import (entrate).

    From this macro i would want to import the variable/value and insert in the related cells in this mode:

    With the index variable AGENZIA (is only the define where i insert a value) find this value in the column B.
    Example:
    the variable AGENZIA is equal "00500" find the line in B and insert other value is the line 6, the variable AGENZIA is 01000 the line is 8 ecc...

    After i have intercepted the line where i inset the other variable from the txt file insert:
    Variable AGENZIA is 00500 insert in D6 the variable MATRICOLA in F6 and the variable DATA_RIC in D6 ecc...

    IN the sheet i have insert various value for example....

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

    Re: intercept the line (2000 sr 1)

    Sub IMPORT_VERSAMENTI()
    Dim riga As String
    Dim cont As Integer
    Dim Vfile As String
    Dim oCell As Range
    Dim Agenzia As Long
    Dim Matricola As String
    Dim Data_Ric As String

    Vfile = "E:MACROFASCIANOVERSAMENTI_OK.TXT"

    Open Vfile For Input As #1

    While Not EOF(1)
    Line Input #1, riga
    If Len(Trim(riga)) > 0 Then
    If InStr(riga, "--") > 0 Then
    Agenzia = Trim(Mid(riga, 31, 5))
    Set oCell = Range(Range("B5"), Range("B65536").End(xlUp)).Find(What:=Agenzia, LookAt:=xlWhole)
    If Not oCell Is Nothing Then
    Matricola = Trim(Mid(riga, 15, 7))
    Data_Ric = Trim(Mid(riga, 139, 5))
    Range("F" & oCell.Row) = Matricola
    Range("D" & oCell.Row) = Data_Ric
    End If
    End If
    End If
    Wend

    Close #1

    Range("A3").Select

    MsgBox "MACRO TERMINATA!"
    End Sub

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

    Re: intercept the line (2000 sr 1)

    Sorry Hans, have you tested?
    Attention the value in the column B are linked from anoter sheet...
    You macro work only if the value in the column B are not linked a value...

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

    Re: intercept the line (2000 sr 1)

    The values are linked to a workbook that I don't have (Prenotazioni_elab.xls).

    See if it works if you change the line

    Dim Agenzia As Long

    to

    Dim Agenzia As String

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

    Re: intercept the line (2000 sr 1)

    Nothing...

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

    Re: intercept the line (2000 sr 1)

    Change the line beginning with Set oCell = ... to

    Set oCell = Range(Range("B5"), Range("B65536").End(xlUp)).Find(What:=Agenzia, LookIn:=xlValues, LookAt:=xlWhole)

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

    Re: intercept the line (2000 sr 1)

    ... IT WORK.

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

    Re: intercept the line (2000 sr 1)

    ORA: 38313,4421585648
    In this string is present a serial time:

    38313 Date
    4421585648 Hour

    I want transform this string during the import directly in date and hour

    Example:
    DATA_RIC = Trim(Mid(RIGA, 139, 5))
    ORA_RIC = Trim(Mid(RIGA, 145, 10))

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

    Re: intercept the line (2000 sr 1)

    Format the DATA_RIC column as a date.
    Format the ORA_RIC column as a time.
    Set the values in the ORA_RIC column to Val("0." &ORA_RIC)

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

    Re: intercept the line (2000 sr 1)

    ... but i want joined date and hour in the same cell.
    this is my idea:

    DATA_RIC = Format(Trim(Mid(RIGA, 139, 5)), "DD/MM/YYYY")
    ORA_RIC = Format(Trim(Mid(RIGA, 145, 10)), "HH:MM")

    Range("D" & ORIZCELL.Row) = DATA_RIC + ORA_RIC

    and i want have 22/11/2004 10:36

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

    Re: intercept the line (2000 sr 1)

    Format the DATA_RIC column with a date + time format.
    Set it like this:

    Range("D" & oCell.Row) = Data_Ric & "." & Ora_Ric

    See attached workbook.

Posting Permissions

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