Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Open Excel 2003 from Windows Explorer | Excel Discussion (Misc queries) | |||
Need suggestions for some uses of Ms Excel | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |