Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 01-07-2009
Member
 
Join Date: Apr 2009
Posts: 65
How to find value in Excel column and then fill down to a particular row

I need a macro that searches for a column in a data bank by heading. In my excel sheet have written a macro to format some data. However there is a section of the macro that fills a column with a formula from the 11th row to the last used row. When you use a range this way it sets the formula of ALL cells in that Range to that formula. I think it may have something to do with the .UsedRange.Rows.Count part.
Reply With Quote
  #2  
Old 01-07-2009
Member
 
Join Date: Dec 2008
Posts: 161
Re: How to find value in Excel column and then fill down to a particular row

Try the following code... this will search for a particular string (partial strings will also work) and then it will add 20 more rows of data to the last cell. I want to do a special fill down that goes to, but not past, the lowest filled cell on the spreadsheet. I can find the last-filled cell in a single column.

Code:
Sub Fill20Down()
    Dim rng As Range
    Set rng = ActiveCell.End(xlDown)
    Range(rng, rng.Offset(20, 0)) = rng
End Sub
Reply With Quote
  #3  
Old 01-07-2009
Member
 
Join Date: Dec 2008
Posts: 202
Re: How to find value in Excel column and then fill down to a particular row

If you column is populated, then you would hit the End Key, then the down arrow. Select the cells in the column, starting in the row below the column heading. Choose Edit | Go To special button, Type an equal sign, Press the up arrow on the keyboard -- this will enter a reference to the cell above, If you want to select from the current cell down to the bottom, then hold down the shift key while you hit End, then the down arrow.
Reply With Quote
  #4  
Old 01-07-2009
Member
 
Join Date: Jan 2006
Posts: 211
Re: How to find value in Excel column and then fill down to a particular row

You may also use the following code in your program and let me know what output you get, this code i found on the internet.

Code:
Sub Fill_Empty()
 '--David McRitchie,  2003-07-24,  fillempt.htm 
 '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells
 'http://www.contextures.com/xlDataEntry02.html
 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html
    Dim oRng As Range
    Set oRng = Selection
    Selection.Font.Bold = True
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Font.Bold = False
    Selection.FormulaR1C1 = "=R[-1]C"
    oRng.Copy
    oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
End Sub
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to find value in Excel column and then fill down to a particular row"
Thread Thread Starter Forum Replies Last Post
How to fill color in to the Row of Microsoft Excel Shaina Na MS Office Support 6 14-01-2012 01:29 PM
How to find particular column name by using Excel Macro Eleder Windows Software 5 05-04-2011 10:49 PM
Find The Value of one column in another column Charth Windows Software 3 23-02-2011 03:01 AM
How to Add Cell Fill in Excel peterjack Windows Software 1 09-02-2011 02:21 AM
What is Fill Handle in Microsoft Excel Chandranath Windows Software 3 25-06-2009 06:53 PM


All times are GMT +5.5. The time now is 12:24 PM.