Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
..EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious, MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.


Why???
__________________________________________________ _____________________

"DaveM" wrote in message
...
Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
.EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious, MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

Dim statements should be at the top of the macro. for arrays, you can use a
Redim to make the array larger or smaller.

"DaveM" wrote:

Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
..EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious, MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

It appears that a look in the vba help index for FINDNEXT would be helpful.
Follow the example and do your best to remove the selections as they are not
necessary or desirable. If all else fails, tell us exactly what you are
trying to do with an example.


--
Don Guillett
SalesAid Software

"DaveM" wrote in message
...
Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
.EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious, MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

all ok, Thanks for your help
"Don Guillett" wrote in message
...
It appears that a look in the vba help index for FINDNEXT would be
helpful. Follow the example and do your best to remove the selections as
they are not necessary or desirable. If all else fails, tell us exactly
what you are trying to do with an example.


--
Don Guillett
SalesAid Software

"DaveM" wrote in message
...
Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
.EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious,
MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

Glad to help. In the future, please reply to original posts

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
. uk...
all ok, Thanks for your help
"Don Guillett" wrote in message
...
It appears that a look in the vba help index for FINDNEXT would be
helpful. Follow the example and do your best to remove the selections as
they are not necessary or desirable. If all else fails, tell us exactly
what you are trying to do with an example.


--
Don Guillett
SalesAid Software

"DaveM" wrote in message
...
Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
.EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious,
MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave

My usenet account was not posting my messages, I've uninstalled and
reinstalled my account for newsgroups. things were a little mixed up. My
apologises.


"Don Guillett" wrote in message
...
Glad to help. In the future, please reply to original posts

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
. uk...
all ok, Thanks for your help
"Don Guillett" wrote in message
...
It appears that a look in the vba help index for FINDNEXT would be
helpful. Follow the example and do your best to remove the selections as
they are not necessary or desirable. If all else fails, tell us exactly
what you are trying to do with an example.


--
Don Guillett
SalesAid Software

"DaveM" wrote in message
...
Hi all

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2007 by Grandad
'

'Always start in R2C16 loop down one cell at a time, in column P.
Application.Goto Reference:="R2C16"

Dim FoundCell As Range

Set FoundCell = ActiveCell.EntireColumn.Find(What:=ActiveCell.Valu e, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
FoundCell.Select

'I need the "Do" statement above the "Dim" sataement,Is there a way of
getting around this problem.

Do
ActiveCell.Offset(0, -13).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 13).Range("A1").Select

On Error Resume Next
With ActiveCell
.EntireColumn.Find(What:=.Text, After:=.Cells(1, 1), LookAt:=xlWhole, _
LookIn:=xlValues, SearchDirection:=xlPrevious,
MatchCase:=False).Select
End With
ActiveCell.Offset(0, -11).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop Until IsEmpty(ActiveCell.Offset(0, 11))

End If

End Sub


Any help appreciated

Thanks in advance

Dave