Dividing Variable Ranges or Strings

ohFice

New Member
Joined
May 30, 2019
Messages
20
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!




Code:
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,906
Office Version
2010
Platform
Windows
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!




Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,011
Office Version
2016
Platform
Windows
Also, probably :
Code:
Dim AnnualizeMonths As [COLOR=#ff0000]Range[/COLOR]
And this :
Code:
AmountsRange = [IFERROR((AmountsRange/AnnualizeMonths)*12), AmountsRange)]
Should probably be this :
Code:
AmountsRange = Evaluate("IFERROR((" & AmountsRange.Address & "/" & AnnualizeMonths.Address & ")*12)," & AmountsRange.Address & ")")
 
Last edited:

ohFice

New Member
Joined
May 30, 2019
Messages
20
****, 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! :)


Code:
[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

New Member
Joined
May 30, 2019
Messages
20
Adjusted to this, and still no luck. The macro is running; however, I am getting #VALUES across the P and R columns :(

Code:
[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

Well-known Member
Joined
Sep 21, 2016
Messages
3,011
Office Version
2016
Platform
Windows
Try :
Code:
AmountsRange = Evaluate("IFERROR(" & AmountsRange.Address & "/" & AnnualizeMonths.Address & "*12," & AmountsRange.Address & ")")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,007
Messages
5,465,976
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top