• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 637
  • Last Modified:

Excel Macro Question - Autofill formula

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
Hags2Rags
Asked:
Hags2Rags
1 Solution
 
Chris BottomleyCommented:
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
 
ragnarok89Commented:

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
 
Hags2RagsAuthor Commented:
Dude, Chris.  Thanks for the quick response.  This is exactly what I was looking to do!  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now