copy:then go to next row ....

[flow23]

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]

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

[flow23]

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
>