I am trying to find a "1" in a column between rows 5 to 913 and replace
the "1" with data that is held in row 4 of the same column, I have 263
columns to do. Using the Find and Replace on each column is taking
ages, can I do this a quicker way? Would realy appreciate help


--
lorraine knight
------------------------------------------------------------------------
lorraine knight's Profile: http://www.excelforum.com/member.php...o&userid=35294
View this thread: http://www.excelforum.com/showthread...hreadid=550786

Hi Lorraine,

you can use Visual Basic.
Press alt+F11 to open the VBE
in the left hand upper pane right click and select insertmodule

paste this code into the code window in the centre

Sub Macro1()
For i = 1 To 256
Cells(4, i).Select
x = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1:A909").Select
Selection.Replace What:="1", Replacement:=x, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next i
End Sub


Press F5 to execute the code.

Does this work as required.
NB there are only 256 columns in a worksheet.

let me know if this works

somethinglikeant







lorraine knight wrote:
I am trying to find a "1" in a column between rows 5 to 913 and replace
the "1" with data that is held in row 4 of the same column, I have 263
columns to do. Using the Find and Replace on each column is taking
ages, can I do this a quicker way? Would realy appreciate help


--
lorraine knight
------------------------------------------------------------------------
lorraine knight's Profile: http://www.excelforum.com/member.php...o&userid=35294
View this thread: http://www.excelforum.com/showthread...hreadid=550786

Hi somethinglikeant,

Your code worked like a dream, its probably saved me 4 hours of data
entry, thanks so much, its realy appreciated. I now have to copy
this data in each of the 976 rows, and paste it to a column in another
spread sheet, I hope I can do this using "transpose" do you know how I
can not include any blank cells when I copy?

Thanks again for you help

best

Lorraine


--
lorraine knight
------------------------------------------------------------------------
lorraine knight's Profile: http://www.excelforum.com/member.php...o&userid=35294
View this thread: http://www.excelforum.com/showthread...hreadid=550786