[EXCEL] Apply a formula to a column

>Antoine, Tuesday, July 24, 2007 at 4:36:11 PM
>(...)
>Basically, I don't want to have to use the incremental copy.
Right on point for my problem. Even after all this time.

>Alain42, Tuesday, July 24, 2007 at 5:47:18 PM
>And there you can then use the incremental copy.
Right on the manipulation to avoid... It's super cumbersome and totally shaky at the slightest modification, especially related to a DBMS. Furthermore, the statement was precise (rare on forums for me to highlight it with a big thank you).

>Vaucluse, Wednesday, July 25, 2007 at 8:20:14 PM
>Just in case, to avoid copying on all rows
Ah, an interesting lead?

>Type the formula corresponding to the first row
>Close with ctrl+ enter
Yeah... Another thing to type by hand...

I write in general: I hope the comments come from a secretary's perspective. No, there is no negative aspect to perceive in the previous sentence: I am only talking about what I was taught, that is to say that caricaturally, a secretary (or even a clerk or paper pusher) simply contents themselves schematically with typing and copy/pasting. It's a long and tedious job.

But from an IT perspective (therefore thoughtful), the problem becomes much easier (provided one acts in an orderly manner). Without being an expert (or even a novice in the field), I was able to find the answer, which I write here for all those who may need it (including myself if I forget in the meantime).

I nonetheless started from the hint given in passing by Vaucluse. But by adding a bit of my own touch: macros. We create a new macro (long live the "internet tutorials") by executing the instructions. Once Ctrl+Enter is done, we finish the recording. And we look at the macro and then analyze it.

The addition that was made is the ability to execute the macro when returning to the cell that was pointed to by the cursor. With the following, the case is to copy data from column A to column B: no utility in itself, I agree, but the goal here must be primarily instructional.

And we get this:

 Sub MacroFormuleColonneB() Call MacroFormuleColonne("B", "=RC[-1]") End Sub Sub MacroFormuleColonne(LettreColonne As String, Formule As String) 'Declarations Dim CelluleSave Dim ConvertCountColumn As Integer Dim ConvertCountRow As Integer Dim ConvertCountCell As String Dim ConvertCountCharA As Integer 'Initialisations CelluleSave = ActiveCell.Address ConvertCountColumn = ActiveCell.Column ConvertCountRow = ActiveCell.Row ConvertCountCharA = Asc("A") ConvertCountCell = Chr$(ConvertCountCharA + (ConvertCountColumn - 1)) & ConvertCountRow 'Traitement Columns(LettreColonne & ":" & LettreColonne).Select Selection.FormulaR1C1 = Formule Range(ConvertCountCell).Select End Sub 

Depending on everyone's issue, it will be easy to adapt.

In addition, this solution allows, upon loading the Excel sheet, to automatically call our column macro. In the VB linked to Excel, we write in the right place* (i.e., the VB of the sheets and not that of the VB macros and modules):

 Private Sub Worksheet_Activate() Call MacroFormuleColonneB End Sub 

* There is a VB project explorer once it is in the active window.

Thus, I hope this long paragraph demonstrates that it is possible to do simple and effective things. Purists will say that this requires the use of macros that must be activated, that there are risks, etc. As for the risks, no, there are none except for macros that we create ourselves or that we understand how they work. Each situation is unique but this will address the majority of cases. Have a good day everyone.

P.S. Certainly, this solution is limiting: it only works on the first 26 columns. Once again, if your case does not match, it needs to be adapted. As always, it is possible to do so.

Two lines are unnecessary and should only remain in drafts:

 Dim CelluleSave 

and

 CelluleSave = ActiveCell.Address 

They can therefore be removed (code optimization).

mmm, I'm looking for a similar function

a table should calculate page implementations
so I have a reference cell giving the number of sheets

I'm trying to apply the formula to all the calculation cells, with a calculated result by valid cell and 0 if I exceed the reference. So my formula is good :-)
but if I copy it into the other cells, my reference cells change too
and I don't know how to copy while fixing certain elements in the formula that need to remain static

not easy to explain, a small example:

IF(K34<=F18;A33+2;0)

the analysis actually goes from K34 to K210
F18 is the reference, so it should always stay F18
A33 increments up to A209
as indicated by the formula, if K.. exceeds the reference F18, the calculated cell becomes "0"

It's not a major issue if it's not possible, but I like finding things that make my life easier.

Thanks in advance

Hello
Maybe a bit late, the answer:
You simply need to lock the reference cell with the $ sign
IF(K34<=$F$18;A33+2;0)
For your information:
$F18 locks the column when you move the cell down the row
F$18 locks the row when you move across the column
$F$18 locks in both directions
Best regards
--
Truth is not possessed, it is only sought