MS Excel - Offset function when using Macro to enter data?

Question

I have created a Macro that takes data from two external Excel documents, and then tabulates them in a master excel sheet.  My issue is that this is done on a daily basis; but the data overwrites the same line over and over again.

So:
            Col A   Col B      Col C    Col D   Col E        Col F              Col G
           June 1 | June 2 | June 3 | June 4 | TODAY | TOMORROW | 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Job 1 |   95%  |   93%   |  98%   |   91%  |  78%   |  
Job 2 |   92%  |   93%   |  95%   |   91%  |  88%   |  

If I were to run my Macro, it always overwrites the TODAY column (column E); When tomorrow comes, and I run the same Macro, it will overwrite Column E, again and again.  

Is there a way to always shift over one cell each time the Macro is run?   So if I run the Macro on TODAY (Column E); and then run the Macro again it will autopopulate Column F; and then G, etc.

Please help!!!!

garbo7441 · Accepted Answer

You can use the Offset parameter in conjunction with End(xlToLeft).

For example, in row 1 the following code line will select the next available column for data entry:

Range("IV1").End(xlToLeft).Offset(0, 1).Select

This tells Excel to locate the last column in row 1 containing data and select the cell '0' rows down and '1' column to the right of that column.

You can use this in a loop for a column.  Here is an example that, each time the macro is called will insert the row number in the next available column to the right.  Enter random data in column A as far down as you wish. Then run this macro 3 or 4 times to view the 'mechanics'.

Sub Find_LastCol()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow
Cells(i, "IV").End(xlToLeft).Offset(0, 1).Value = i
Next
End Sub

? · Answer

Your question is extremely confusing in that i'm uncertain what you're attempting to do. You point out 'in Excel fill' -- are you filling the 'countif' formulation down a column, as a result you have distinctive formulation to handle? Do you have the formulation in only one cellular? do you prefer the formulation to continually function on the comparable selection, i.e. A1:E40? Or, do you prefer the kind to be dynamic and alter to comprise new cells extra?

Blackened · Answer

the following should give you the last unused cell in row 1 and place the date in that cell.

dim startcell as range
set startcell = range("IV1").end(xltoleft).offset(0,1)
startcell.value = Date

MS Excel - Offset function when using Macro to enter data?

Related Questions

Trending Questions