Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro reqd (Excel 2000)

    I have spread sheet which has data in Col A to Col D. Each cell in Col A will have some data but in Col D only some cells will have data. I require a macro which will pickup the data ( numerals and alphabets) from Col D and and paste same in Col A overwriting the existing data in the cell. I have attached a sample workbook. Can anyone help me with this macro.

    Novice

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Does this do what you want?

    <pre>Option Explicit

    Public Sub CopyData()
    Dim oCell As Range
    For Each oCell In Range(Range("A1"), Range("A65536").End(xlUp))
    If oCell.Offset(0, 3).Value <> "" Then oCell.Value = oCell.Offset(0, 3).Value
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Thanks for the quick response. I am not sure how to place this macro in the workbook. Pls guideme in doing this. I am attaching the workbook with sheet of raw data and what should hapen after the macro is run.
    Novice

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    OK, the attached workbook has the macro included.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Thanks a lot for the macro. How do i copy this in other workbooks.
    Novice

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

    Re: macro reqd (Excel 2000)

    When you have two workbooks open, you can drag a module from one to the other in the Project Explorer in the Visual Basic Editor. This will copy the module, with all code contained in it.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Hans,
    Thanks for your tip. I am attaching another file as it seems that the requirement is slightly different. In the attached file in the Data sheet the entries in Col F have to copied into the Col A which can be done by slightly modifying the Macro written as above. However, in Col G only the entries begining with IMO and RF should be copied to Col A (the entries begining with RF should be copied as REEFER in Col A).

    Also in the Final Booking sheet the Macro should delete all entries after a cell value in Col C is detected as 0 Zero) In the attached sheet the entries from row 12 should be deleted when the macro is run.
    Can you help me with this Macro.

    Thanks in advance
    Novice

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

    Re: macro reqd (Excel 2000)

    For the DATA sheet: what if there is an entry in column F and also an entry in clumn G starting with IMO or RF? Or does that never happen?

    For the Final Booking sheet, you can use this macro:

    Sub DeleteFrom0()
    Dim wsh As Worksheet
    Dim rng As Range
    ' Set reference to worksheet
    Set wsh = Worksheets("Final Booking")
    ' Does column C contain a 0 value?
    Set rng = wsh.Range("C:C").Find(What:=0, After:=wsh.Range("C1"), _
    LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    ' Yes, so delete everything from this row down.
    wsh.Range(rng.Row & ":65536").Delete
    End If
    Set rng = Nothing
    Set wsh = Nothing
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Hans,

    For the DATA sheet the entry will be either in Col F or in COL G or none at all but cannot be both in Col F and Col G.
    Thanks N

    Novice.

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

    Re: macro reqd (Excel 2000)

    Try this:

    Public Sub CopyData()
    Dim wsh As Worksheet
    Dim oCell As Range
    Set wsh = Worksheets("Data")
    For Each oCell In wsh.Range(wsh.Range("A2"), wsh.Range("A65536").End(xlUp))
    If oCell.Offset(0, 5).Value <> "" Then
    oCell.Value = oCell.Offset(0, 5).Value
    ElseIf Left(oCell.Offset(0, 6).Value, 3) = "IMO" Then
    oCell.Value = oCell.Offset(0, 6).Value
    ElseIf Left(oCell.Offset(0, 6).Value, 2) = "RF" Then
    oCell.Value = "REEFER"
    End If
    Next oCell
    Set oCell = Nothing
    Set wsh = Nothing
    End Sub

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Hans,

    Thanks for both the macros which work perfectly. However, the second macro is transferring a blank entry from Col F to Col A ( row 10). Any reasons for this?
    Thanks in sdv.
    Novice.

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

    Re: macro reqd (Excel 2000)

    That is because F10 is not really blank, but contains a space. You can work around this by replacing the line

    If oCell.Offset(0, 5).Value <> "" Then

    with

    If Trim(oCell.Offset(0, 5).Value) <> "" Then

  13. #13
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro reqd (Excel 2000)

    Hans,

    The macro works perfectly now. Without you and other Excel Gurus office work would definitley be more tedious. Thanks for all the help.

    Novice.

Posting Permissions

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