Simon - the rogue '-1'

Hi Jen,

Thank for posting that question and welcome to the forum.

I have sinced realised this is a terrible way of doing the code and I have found it impossible to explain the -1 in a different way even after speaking to a friend.

The better way of doing it would be to start the Currentregion.count from 9 and then start the loop from two so it applies the functions from row 10 to the bottom. I think this method is more transparent and is easier to follow:

Sub CommMarkup()

Dim intColumn As Integer


intColumn = Sheets(strName).Range("a9").CurrentRegion.Columns.Count
'counts number of columns intcount = last column selling price
For intRowCount = 2 To Sheets(strName).Range("a9").CurrentRegion.Rows.Count
'In the next column after the last column(Selling Price)
'intcolumn is always referenced from the last column which at the time was Selling Price

'Apply the MarkUp function in the next blank column
'Markup arguments are DP followed by Selling Price

Sheets(strName).Range("a9").Cells(intRowCount, intColumn + 1) = _
MarkUp(Sheets(strName).Range("a9").Cells(intRowCount, intColumn - 1), _
Sheets(strName).Range("a9").Cells(intRowCount, intColumn))
'Apply the Commission formula using the Markup value created above
Sheets(strName).Range("a9").Cells(intRowCount, intColumn + 2) = _
Commission(Sheets(strName).Range("a9").Cells(intRowCount, intColumn + 1))

Next intRowCount ' increase the row number by one

Sheets(strName).Range("J9") = "Mark Up"
Sheets(strName).Range("K9") = "Commission"


End Sub

I hope this helps and sorry for the delay in responding.

Regards

Simon