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

    INSERT VALUE DURING THE MACRO (2000 sr 1)

    ... This macro make a copy and delete line from a sheet to another sheet. (Tks to HansV)
    I want, during this process, copy also the value of the column G of the sheet L085_TOTALE in the column X of the sheet L085_CDI_50...

    <font face="Script MT Bold">Sub ASS_CONT_A_CDI50()

    Dim lngSourceRow As Long
    Dim lngSourceMaxRow As Long
    Dim lngTargetRow As Long
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet

    On Error GoTo ErrHandler

    Application.ScreenUpdating = False

    ' Set reference to worksheets
    Set wshSource = Worksheets("L0785_TOTALE")
    Set wshTarget = Worksheets("L0785_CDI_50")
    ' Last row in source sheet
    lngSourceMaxRow = wshSource.Range("A65536").End(xlUp).row
    ' Last row in target sheet
    lngTargetRow = wshTarget.Range("A65536").End(xlUp).row
    ' Loop backwards through source rows
    For lngSourceRow = lngSourceMaxRow To 7 Step -1
    ' Test condition
    If wshSource.Range("T" & lngSourceRow) = "ASS. CONT. A CDI 50" Then
    ' Next target row
    lngTargetRow = lngTargetRow + 1
    ' Copy to target sheet
    wshSource.Range(wshSource.Cells(lngSourceRow, 1), wshSource.Cells(lngSourceRow, 20)).Copy wshTarget.Cells(lngTargetRow, 1)
    ' Delete from source sheet
    wshSource.Rows(lngSourceRow).Delete
    End If
    Next lngSourceRow

    ExitHandler:
    Set wshSource = Nothing
    Set wshTarget = Nothing

    Application.ScreenUpdating = False

    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub
    </font face=script>

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    That would be a duplicate value, since the call in column G in L0785_TOTALE is already copied to column G in L0785_CDI_50 (or did you really mean L085_CDI_50?)

    Below the line

    wshSource.Range(wshSource.Cells(lngSourceRow, 1), wshSource.Cells(lngSourceRow, 20)).Copy wshTarget.Cells(lngTargetRow, 1)

    add this line (column 7 = G, column 24 = X):

    wshSource.Cells(lngSourceRow, 7).Copy wshTarget.Cells(lngTargetRow, 24)

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    uhm...

    I have make that but if i dont have mistake your correction not copy G in X

    If you want make a test use the WBOOK L0785_DEF.ZIP in the old post.
    the amcro is Sub ASS_CONT_A_CDI50()

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    HansV, SORRY...................

    Dont worry....

    I am a stupid...


    Naturally all OK!

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    Hans, Hans, Hans...

    Your suggestion work correctly but not is good for my ideas.
    I have make the ordering by G but the column is in txt format an not is possible to order from this param.
    In effect i wolud want to order the attached sheet from G and my idea is to copy the value in the column X of the sheet L0785_CDI_50 and make ordering with this:

    Sub ORD_ASS_CDI_50()
    Sheets("L0785_CDI_50").Select
    Range("A7:X7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("X7"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A7").Select
    End Sub

    The column X in the sheet is in txt format have you another idea...

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    Insert this code below Sheets("L0785_CDI_50").Select:

    With Range(Range("X7"), Range("X65536").End(xlUp))
    .NumberFormat = "0.00"
    .TextToColumns Destination:=Range("X7")
    End With

    This converts the values in column X to numbers. You could also apply this to column G itself, then sort on G.

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    OK! BUT WHERE ...
    I have many Sheets("L0785_CDI_50").Select in my project...
    Peraphs in the macro ordering?

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    In the Sub ORD_ASS_CDI_50() you mentioned in your previous post.

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    Hans, is possible to multiplicate *1 this value when this line is copied in column X?
    wshSource.Cells(lngSourceRow, 7).Copy wshTarget.Cells(lngTargetRow, 24)

    Note: tks for the last suggestion (History.....)

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    Like this, for example:

    ' Copy value to column X
    wshSource.Cells(lngSourceRow, 7).Copy wshTarget.Cells(lngTargetRow, 24)
    ' Multiply result by 1
    wshTarget.Cells(lngTargetRow, 24) = wshTarget.Cells(lngTargetRow, 24) * 1

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

    Re: INSERT VALUE DURING THE MACRO (2000 sr 1)

    Tks, Work fine...

Posting Permissions

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