Solved

Excel Macro Question - Autofill formula

Posted on 2010-09-17
3
623 Views
Last Modified: 2012-05-10
Hey everyone,

I am working on a macro in excel and I am having some issues with an autofill function.  I am trying to autofill a formula for all rows that have a value in column C.  My issue with my current macro is this....
when i recorded the macro, the macro recorded that i wanted to autofill the formula for cells D2:D539.  
VBA code example:
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-2]),R[-1]C,(RC[-2]))"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D539")


The number of rows will change each time I run this macro, could be more, could be less.  That is why I would like to apply this formula to only cells where the Same row column C has a value.  Is there a code i can type into this VBA string to help?
Thanks!
0
Comment
Question by:Hags2Rags
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 33704247
If you mean fill down to the last row occupied in column C then:

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-2]),R[-1]C,(RC[-2]))"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row)

Chris
0
 
LVL 8

Expert Comment

by:ragnarok89
ID: 33704330

for r = 2 to 5000 'assumes you'll always have less than 5000 rows
   if range("C" & r).value <> "" then range("D" & r).FormulaR1C1 = "=IF(ISBLANK(RC[-2]),R[-1]C,(RC[-2]))"
0
 

Author Closing Comment

by:Hags2Rags
ID: 33704372
Dude, Chris.  Thanks for the quick response.  This is exactly what I was looking to do!  
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question