Dividing Variable Ranges or Strings

[ohFice]

I am trying to get a range to divide itself by itself divided by a different range, and then multiplied by 12. I wrong the code below in an attempt to achieve this, and keep getting an error stating "Compile error: Object required".

I have tried changing the "AnnualizeMonths" and "AmountsRange" variables from Strings to Ranges, and also get an error.

Does anybody know what I am going wrong? Thanks for the help in advance!

Sub Annualize()

'Annualizes Amounts Populated Within The Amounts Column ("P") as Dynamic Range


Dim AnalystWorkbook As Workbook
Set AnalystWorkbook = ThisWorkbook


Dim CashFlow As Worksheet
Set CashFlow = AnalystWorkbook.Worksheets("Cash Flow")


Dim StartCell As String
Set StartCell = AnalystWorkbook.CashFlow.Range("P59")
Dim EndCell As Range
Set EndCell = AnalystWorkbook.CashFlow.StartCell.End(xlDown)
Dim AmountsRange As Range
Set AmountsRange = AnalystWorkbook.CashFlow.Range(StartCell, EndCell)


Dim AnnualizeMonths As String
Dim FirstMonthValue As Range
Dim LastMonthValue As Range
Set FirstMonthValue = Range("R59")
Set LastMonthValue = AnalystWorkbook.CashFlow.AmountsColumn.Offset(0, 1)
AnnualizeMonths = AnalystWorkbook.CashFlow.Range(FirstMonthValue, LastMonthValue)


AmountsRange = [IFERROR((AmountsRange/AnnualizeMonths)*12), AmountsRange)]


End Sub

 

[Rick Rothstein]

I am trying to get a range to divide itself by itself divided by a different range, and then multiplied by 12. I wrong the code below in an attempt to achieve this, and keep getting an error stating "Compile error: Object required".

I have tried changing the "AnnualizeMonths" and "AmountsRange" variables from Strings to Ranges, and also get an error.

Does anybody know what I am going wrong? Thanks for the help in advance!

Sub Annualize()

'Annualizes Amounts Populated Within The Amounts Column ("P") as Dynamic Range


Dim AnalystWorkbook As Workbook
Set AnalystWorkbook = ThisWorkbook


Dim CashFlow As Worksheet
Set CashFlow = AnalystWorkbook.Worksheets("Cash Flow")


Dim StartCell As [B][COLOR="#FF0000"]String[/COLOR][/B]
Set StartCell = AnalystWorkbook.CashFlow.Range("P59")
Dim EndCell As Range
Set EndCell = AnalystWorkbook.CashFlow.StartCell.End(xlDown)
Dim AmountsRange As Range
Set AmountsRange = AnalystWorkbook.CashFlow.Range(StartCell, EndCell)


Dim AnnualizeMonths As String
Dim FirstMonthValue As Range
Dim LastMonthValue As Range
Set FirstMonthValue = Range("R59")
Set LastMonthValue = AnalystWorkbook.CashFlow.AmountsColumn.Offset(0, 1)
AnnualizeMonths = AnalystWorkbook.CashFlow.Range(FirstMonthValue, LastMonthValue)


AmountsRange = [IFERROR((AmountsRange/AnnualizeMonths)*12), AmountsRange)]


End Sub

I haven't looked beyond this as it jumped right out at me, but what I highlighted in red looks like it should be Range instead of String... the next line uses Set which cannot be used on a String variable.

 

[footoo]

Also, probably :

Dim AnnualizeMonths As [COLOR=#ff0000]Range[/COLOR]

And this :

AmountsRange = [IFERROR((AmountsRange/AnnualizeMonths)*12), AmountsRange)]

Should probably be this :

AmountsRange = Evaluate("IFERROR((" & AmountsRange.Address & "/" & AnnualizeMonths.Address & ")*12)," & AmountsRange.Address & ")")

 

[ohFice]

****, I messed around with it a lot while awaiting a reply. No longer getting any errors; however, all it is doing is turning all my "P" column cells and "R" column cells into "#VALUE"s; whereas I did not even want the "R"column to change lol. This is what I currently have. Going to read through both responses and try and implement the code you provide now, Thanks Footoo suuuper appreciate it!

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Annualize()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'Annualizes Amounts Populated Within The Amounts Column ("P") as Dynamic Range[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim AnalystWorkbook As Workbook
Set AnalystWorkbook = ThisWorkbook[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim CashFlow As Worksheet
Set CashFlow = AnalystWorkbook.Worksheets("Cash Flow")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim StartCell As Range
Set StartCell = CashFlow.Range("P59")
Dim AmountRange As Range
Set AmountRange = Range(Range("P59"), Range("P59").End(xlDown))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim AnnualizeMonths As Range
Dim FirstMonthValue As Range
Set FirstMonthValue = CashFlow.Range("R59")
Set AnnualizeMonths = Range(Range("R59"), Range("R59").End(xlDown))[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]AmountRange = [IFERROR((AmountRange/AnnualizeMonths)*12), AmountRange)][/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[/FONT]

 

[ohFice]

Adjusted to this, and still no luck. The macro is running; however, I am getting #VALUES across the P and R columns

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Annualize()

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'Annualizes Amounts Populated Within The Amounts Column ("P") as Dynamic Range

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim AnalystWorkbook As Workbook
Set AnalystWorkbook = ThisWorkbook

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim CashFlow As Worksheet
Set CashFlow = AnalystWorkbook.Worksheets("Cash Flow")

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim StartCell As Range
Set StartCell = CashFlow.Range("P59")
Dim AmountsRange As Range
Set AmountsRange = Range(Range("P59"), Range("P59").End(xlDown))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim FirstMonthValue As Range
Set FirstMonthValue = CashFlow.Range("R59")
Dim AnnualizeMonths As Range
Set AnnualizeMonths = Range(Range("R59"), Range("R59").End(xlDown))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]AmountsRange = Evaluate("IFERROR((" & AmountsRange.Address & "/" & AnnualizeMonths.Address & ")*12)," & AmountsRange.Address & ")")

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[/FONT]

 

[footoo]

Try :

AmountsRange = Evaluate("IFERROR(" & AmountsRange.Address & "/" & AnnualizeMonths.Address & "*12," & AmountsRange.Address & ")")

 

[ohFice]

It worked!! Thanks, Footoo!