#1   Report Post  
Old June 11th 06, 03:01 PM posted to microsoft.public.excel.misc
lorraine knight
 
Posts: n/a
Default 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   Report Post  
Old June 11th 06, 03:37 PM posted to microsoft.public.excel.misc
somethinglikeant
 
Posts: n/a
Default 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   Report Post  
Old June 12th 06, 03:44 PM posted to microsoft.public.excel.misc
lorraine knight
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Excel 2003 from Windows Explorer pmpjr Excel Discussion (Misc queries) 9 September 11th 06 03:58 PM
Need suggestions for some uses of Ms Excel Bible John Excel Discussion (Misc queries) 1 February 27th 06 06:30 PM
Stop excel from dropping the 0 in the beginning of a number? Rosewood Setting up and Configuration of Excel 11 February 12th 06 02:38 AM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM


All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017