Guruza

Reply by Dave 720 days ago

The two keys to this are recognizing that when data is pasted, the data highlighted/selcted in Excel, which means it is accessible in Excel VBA through the object called "Selection". The second thing to take note of is that the Selection object (in this case) is actually a Range object. This means that we can treat "Selection." the same as we would "Range."

One of the things we can do with the Range object is loop through all the cells using For Each, as shown below, where I set the newly pasted cells to 0:

For Each c In Selection.Cells
c.Value = 0
Next

This code can simply be pasted into a macro definition using the built-in VBA editor.

Of course, setting things to zeros isn't all that useful, so we might try multiplying the newly pasted values by 5, like so:

For Each c In Selection.Cells
c.Value = c.Value * 5
Next

That's fine, but maybe you need to reference other nearby cells. In that case, you'll want to use the Offset feature. This example will make each of the newly pasted cells take a value equal to the cell above it plus 5:

For Each c In Selection.Cells
c.Value = c.Offset(-1, 0).Value + 5
Next

The one problem with all these is that it simply provides a value--you can put in equations, but there seems to be no obvious built-in way to dynamically create equations that reference neighboring cells. However, a small hack will get us that ability. By using the "row" and "column" properties of the cell object, we know the numeric position of the cell. This is plenty if the worksheet rows & columns use "R1C1" labeling, but most people use the default, where a cell is labeled with a letter and a number, like "C5". In this instance, you can dynamically create new equations involving neighboring cells by creating the equation with something like this in the macro:

For Each c In Selection.Cells
c.Value = Num2Col(c.Column) & c.Row
Next

And defining a function that converts the column number into the appropriate column name, like this:

Function Num2Col(num As Integer) As String
If (num <= 26) Then
Num2Col = Chr(num + 64)
End If
End Function

The above example will simply print the cell's location within the cell, but the following macro code demonstrates how this can be used in an equation, in this case, multiplying the contents of the previous cell by 5. The difference between this version and the one given earlier is that this one stores an equation and the earlier one stores only the resulting value.

For Each c In Selection.Cells
c.Value = "=" & Num2Col(c.Column) & (c.Row - 1) & "*5"
Next

I should point out that the Num2Col function I defined only works for the first 26 columns--it is possible to define a more robust function (i.e. one that works all the way up to ZZ or whatever your upper limit is), but I've left that as an exercise.

Sorry if this answer has way more detail than you need, but I got curious and wanted to see what all was possible...