Modifying a macro formula

QuestionEdit

I am trying to write a macro that will sort a column of 32 numbers (cells B22:B53) so that they are reordered in ascending order starting with the number that is greater than a value specified by the user in the worksheet (cell K11). For example, if K11 is set to 1002, and the list has even intervals of 5 such that it initially reads as 900, 905, ..., 1050, 1055, then using the macro, it would change to 1005, 1010, ..., 1050, 1055, 900, 905, ..., 995, 1000.

I am able to accomplish this task if I do not involve cell K11 and hardcode in its value. The macro that does this is the following:

Sub Reorder_List() Dim r As Range

Set r = Range("B22").Resize(32, 1) r.Offset(0, 29).FormulaR1C1 = "=if(RC[-29] < 1002, RC[-29]+10000, RC[-29])"

r.Resize(32, 30).Sort Key1:=Range("AE22"), Order1:=xlAscending, Header:=xlNo, MatchCase:=False r.Offset(0, 29).ClearContents

End Sub

Can you tell me how to change this so that cell K11 is substituted in for 1002? I am not well-versed in writing macros, so any help is greatly appreciated.

Tracy

AnswerEdit

Tracy

K11 in R1C1 notation is R11C11 (in absolute terms as opposed to relative which is what you want)

so

Sub Reorder_List() Dim r As Range

Set r = Range("B22").Resize(32, 1) r.Offset(0, 29).FormulaR1C1 = "=IF(RC[-29] < R11C11, RC[-29]+10000, RC[-29])"

r.Resize(32, 30).Sort Key1:=Range("AE22"), Order1:=xlAscending, Header:=xlNo, MatchCase:=False r.Offset(0, 29).ClearContents

End Sub

That worked for me.