Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy
5 columns
Contact Reference Name Date of Transaction Amount Fundcode If there is any data in column 5 then merge column 2 and 3 (in column 2) and copy 4,5 and 6 column in column 3,4 and 5 then go to next row .... pls help with this macro |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy
You sure you don't want to check column 6???
Try this against a copy! Option Explicit Sub testme() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Set wks = ActiveSheet With wks Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, 4)) Then 'do nothing Else .Offset(0, 1).Value _ = .Offset(0, 1).Value & " " & .Offset(0, 2).Value .Offset(0, 2).Value = "" .Offset(0, 2).Delete shift:=xlToLeft End If End With Next myCell End With End Sub If IsEmpty(.Offset(0, 4)) Then checks column 5. Change it to If IsEmpty(.Offset(0, 5)) Then if you want to check column F. flow23 wrote: 5 columns Contact Reference Name Date of Transaction Amount Fundcode If there is any data in column 5 then merge column 2 and 3 (in column 2) and copy 4,5 and 6 column in column 3,4 and 5 then go to next row .... pls help with this macro -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy
thanks very much it works fab.
Yes The column 6 didnt came to my mind in the first place. but I copied the same macro and changed the offsets accordingly. "Dave Peterson" wrote: You sure you don't want to check column 6??? Try this against a copy! Option Explicit Sub testme() Dim wks As Worksheet Dim myCell As Range Dim myRng As Range Set wks = ActiveSheet With wks Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) For Each myCell In myRng.Cells With myCell If IsEmpty(.Offset(0, 4)) Then 'do nothing Else .Offset(0, 1).Value _ = .Offset(0, 1).Value & " " & .Offset(0, 2).Value .Offset(0, 2).Value = "" .Offset(0, 2).Delete shift:=xlToLeft End If End With Next myCell End With End Sub If IsEmpty(.Offset(0, 4)) Then checks column 5. Change it to If IsEmpty(.Offset(0, 5)) Then if you want to check column F. flow23 wrote: 5 columns Contact Reference Name Date of Transaction Amount Fundcode If there is any data in column 5 then merge column 2 and 3 (in column 2) and copy 4,5 and 6 column in column 3,4 and 5 then go to next row .... pls help with this macro -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy tabs(sheets) from workbook without link to original source | Excel Discussion (Misc queries) | |||
Copy shortcut | Excel Worksheet Functions | |||
How can I copy cell formats in functions? | Excel Worksheet Functions | |||
Copy sheet 1 data to sheet 2 cells. | Excel Worksheet Functions | |||
Copy without Hidden Cols - How | Excel Discussion (Misc queries) |