Dividing by a value in a cell that moves

[DarkSilence1979]

Hello all,

So my question is how can I use a value that is calculated in a cell to be a denominator in a formula into other cells? The problem I am having is the cell that is the denominator could move to a different cell the next time this macro is ran. The value will always be in column D of my spreadsheet, but it could move up and down the column, depending on how much data is brought in the next time the macro is ran. If the cell was in a consistent location, this would not be a problem. I hope I explained it. Can someone please help me out? Thank you.

Ryan

 

[Fluff]

How would you determine which cell in Col D to use?
Would it always be the last used cell?

 

[jmacleary]

Hi there. Unfortunately I dont think you have given us enough, but - my guess is that the denominator is a result of a calculation in column D and will be at the end of the data rows in that column. So, if you have 5 rows of data the answer is in row 5, if you have 12 rows of data the answer is in column 12 etc. There are (at least) 2 ways round this - you say its a macro that produces the value - why not change the macro to always put the result in (say) row 2 - or a fixed cell? Or, if its the only value in that column (whatever row it is) then you could use max(D:D) to get it, or as I suspect Fluff is getting at, you could always refer to the last used row in that column.

 

[DarkSilence1979]

I will see if I can explain this better.

Fluff helped me get to this point with the following code to get my denominator value

    Range("G1").Value = "40"
    Cells.EntireColumn.AutoFit
    Range("D:D").NumberFormat = "General"


    Range("D" & Rows.Count).End(xlUp).Offset(2).Resize(, 3).FormulaR1C1 = "=sum(r2c:r[-2]c)"

So I have a value in column D of the sum of the range of values in column D. The SUM value is offset two cells down from the last entry in column D. The above code keeps this type of positioning in column D no matter how many rows of data are in column D. So here comes the issue I am working on. In column E in starting cell E2 I want to have the formula =(D2/"the sum value calculated in the cell that I am calling the denominator") I do not know how to code denominator when it needs to be an absolute, but only to the cell that the code above puts it into. I would post a picture of the spreadsheet, but I do not see how to do that in a forum. Thank you for your time.

Ryan

 

[Fluff]

How about

Sub DarkSilence()
    Dim UsdRws As Long
    Range("G1").Value = "40"
    Cells.EntireColumn.AutoFit
    Range("D:D").NumberFormat = "General"

    UsdRws = Range("D" & Rows.Count).End(xlUp).Row
    Range("D" & UsdRws).Offset(2).Resize(, 3).FormulaR1C1 = "=sum(r2c:r[-2]c)"
    Range("E2:E" & UsdRws).FormulaR1C1 = "=rc[-1]/r" & UsdRws + 2 & "c[-1]"
End Sub

 

[DarkSilence1979]

Fluff,

That works! Your help will make future spreadsheets better.

Ryan

 

[Fluff]

You're welcome & thanks for the feedbacck