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

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

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