Relative reference in macro

[dpistols]

I am trying to create a macro that will move up to the line above that has text, then shoot to the end of the row where the text ends. I used the control + arrow key while recording the macro, but it was one cell short of where I wanted it to be when I tried it. I am using dates, where my macro goes to the last day of the prior month and adds 1. When I did this for August, it went to July 30, instead of to the end of the line.

here is the macro code:
ActiveCell.FormulaR1C1 = "=R[-4]C[29]+1"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWindow.SmallScroll Down:=-3
ActiveCell.Offset(-1, 1).Range("A1").Select

 

[TFCJamieFay]

This piece of code will find the next empty cell to the right of your dates. Is this what you are looking for?

Selection.End(xlToRight).Offset(0, 1).Select

 

[dpistols]

I need it to find the last date in the month. right now it is locked onto a particular cell, in some months this is the end, in others, it is one day short...

 

[stephen antoine]

I'm not sure Iv'e understood your question completely, but try this string of code..

Sub DateAdd()

 FinalRow = Cells(65536, 1).End(xlUp).Row
 FinalColumn = Cells(1, 256).End(xlToLeft).Column
 
 
 
 Do Until FinalRow = Empty
 
 Cells(FinalRow, 1).Select
 If ActiveCell.Value <> "" Then
 Cells(FinalRow, FinalColumn + 1).FormulaR1C1 = "=RC[-1]+1"
 
 FinalRow = FinalRow - 1
 
 Else
 
 FinalRow = FinalRow - 1
 
 End If
 
 Loop
'
End Sub

It's crude, but it will search your data in column 1 to see if it is blank or contains values. If it contains values it will move to the end of the data in the row, take that value (be it data or otherwise) and then subtract one and put the result in the very next cell on the same row....