VBA Auto fill formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2898475%22%20slang%3D%22en-US%22%3EVBA%20Auto%20fill%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2898475%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%20I%20need%20help%20with%20a%20macro%20I%E2%80%99m%20creating%20this%20is%20what%20I%20have%20so%20far%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange(%E2%80%9CJ2%E2%80%9D).Select%3C%2FP%3E%3CP%3EActiveCell.FormulaR1C1%20%3D%20%E2%80%9C%3DIFERROR(RC%5B-1%5D%2FRC%5B-2%5D%2C0)%E2%80%9D%3C%2FP%3E%3CP%3ERange(%E2%80%9CJ2%E2%80%9D).Select%3C%2FP%3E%3CP%3ERange(%E2%80%9CJ2%E2%80%9D).AutoFill%20Range(%E2%80%9CJ2%3AJ2000%E2%80%9D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20put%20J2000%20for%20now%20but%20I%20would%20like%20it%20to%20stop%20as%20soon%20as%20there%20is%20no%20more%20data%20to%20divide.%20There%E2%80%99s%20some%20adjacent%20cells%20that%20could%20be%20blank%20though%2C%20so%20I%20don%E2%80%99t%20want%20it%20to%20stop%20on%20those%20parts%20however%20I%20was%20it%20to%20stop%20wherever%20the%20total%20row%20is%20hit.%20Any%20help%20will%20be%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2898475%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2899721%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Auto%20fill%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2899721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199328%22%20target%3D%22_blank%22%3E%40moczare%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20don't%20use%20curly%20quotes%20%E2%80%9C%20%E2%80%9D%20around%20text%20strings%20in%20VBA.%20Always%20use%20straight%20quotes%20%22%20%22.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20Dim%20LastRow%20As%20Long%0A%20%20%20%20LastRow%20%3D%20Cells.Find(What%3A%3D%22*%22%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlPrevious).Row%0A%20%20%20%20Range(%22J2%3AJ%22%20%26amp%3B%20LastRow).FormulaR1C1%20%3D%20%22%3DIFERROR(RC%5B-1%5D%2FRC%5B-2%5D%2C0)%22%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi guys I need help with a macro I’m creating this is what I have so far

 

Range(“J2”).Select

ActiveCell.FormulaR1C1 = “=IFERROR(RC[-1]/RC[-2],0)”

Range(“J2”).Select

Range(“J2”).AutoFill Range(“J2:J2000”)

 

I just put J2000 for now but I would like it to stop as soon as there is no more data to divide. There’s some adjacent cells that could be blank though, so I don’t want it to stop on those parts however I was it to stop wherever the total row is hit. Any help will be appreciated

1 Reply

@moczare 

Please don't use curly quotes “ ” around text strings in VBA. Always use straight quotes " ".

    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("J2:J" & LastRow).FormulaR1C1 = "=IFERROR(RC[-1]/RC[-2],0)"