Hi Howard,

Am Fri, 31 Jul 2015 07:07:36 -0700 (PDT) schrieb L. Howard:

Another non array situation would be to FIND the value "eex4" or a serial number 1122 (a string or a Long) in column E1:En and return the data from E to the right any given number of columns. Where the data associated with "eex4" may be six columns across and "eex9" only has three columns of data. These would then be copied to A29 and A30.

try it with:

Sub Copy()
Dim FERow As Long, LRow As Long, LCol As Long
Dim c As Range

With ActiveSheet
FERow = WorksheetFunction.Max(29, .Cells(49, 1).End(xlUp).Offset(1,
0).Row)
LRow = .Cells(Rows.Count, 5).End(xlUp).Row
Set c = .Range("E1:E" & LRow).Find("eex4", LookIn:=xlValues,
lookat:=xlWhole)
If Not c Is Nothing Then
LCol = .Cells(c.Row, Columns.Count).End(xlToLeft).Column
If FERow = 50 Then
.Cells(FERow, 1).Resize(columnsize:=LCol - 4).Value = _
.Range(.Cells(c.Row, 5), .Cells(c.Row, LCol)).Value
Else
MsgBox "Exceeding Range"
End If
End If
End With
End Sub

If eex4 could appear more than once you have to do it with FindNext


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional