VBA How to loop until the last used cell

Question

I am trying to get the returns of a stock after each close date and list it in a column. My problem is that the start and end dates change and any cell that is not used for returns has to be completely cleared of contents. Here is what I have so far.

 Sheets("returns").Range("a2").FormulaR1C1 = _ "=IFERROR(returns(Imported!RC[4],Imported!R[1]C[4]),"""")" Sheets("returns").Range("a2").Select Selection.AutoFill Destination:=Range("a2:a937") Range("a2:a937").Select Sheets("returns").Range("c2").FormulaR1C1 = _ "=IFERROR(returns(Imported!RC[10],Imported!R[1]C[10]),"""")" Sheets("returns").Range("C2").Select Selection.AutoFill Destination:=Range("c2:c937") Range("C2:C937").Select

This works for what I need but it leaves a formula in the empty cells which I can't have for the next step of my project. It also leaves a -1 return in the last row when I run out of data. The -1 return isn't too big of a deal if that can't be fixed. Is there a way to clear the contents of a cell that doesn't contain a value but contains a formula?

It is hard to understand what you want in terms of input and output, please post an example of data before and after running the vba code — Ibo
– Ibo, Commented Dec 2, 2017 at 4:41
Can you specify the column which you are referring to work out the last filled row? — shrivallabha.redij
– shrivallabha.redij, Commented Dec 2, 2017 at 5:46

Johnny D · Accepted Answer · 2017-12-02 05:41:26Z

Here’s what I think you want…

You have data in worksheet “Imported”
You want formulas in worksheet “returns” for the same number of rows that exist in worksheet “Imported”

Sub addFormulasBasedOnRecordCount() ' ======================================================== ' jdoxey ' Version 1.0 ' ======================================================== Dim wsWithData As Worksheet ' the name of the worksheet that has the data Dim wsFormulas As Worksheet ' the name of the worksheet that you want the formulas in Set wsWithData = Worksheets("imported") ' change the "name" to be what you want Set wsFormulas = Worksheets("returns") ' change the "name" to be what you want Dim activeRows As Long ' this will be the number of rows that have data ' gets the number of rows in "wsWithData", ' assumes that the data starts in "A1" ' and there are no empty rows activeRows = wsWithData.Range("A1").CurrentRegion.Rows.Count ' puts the formula into column A starting with row 2 though the number of rows in "wsWithData" wsFormulas.Range("A2:A" & activeRows). _ FormulaR1C1 = "=IFERROR(returns(Imported!RC[4],Imported!R[1]C[4]),"""")" ' puts the formula into column C starting with row 2 though the number of rows in "wsWithData" wsFormulas.Range("C2:C" & activeRows). _ FormulaR1C1 = "=IFERROR(returns(Imported!RC[10],Imported!R[1]C[10]),"""")" ' ======================================================== ' ========================================================

End Sub

Collectives™ on Stack Overflow

VBA How to loop until the last used cell

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related