Sum with variable in VBA macro

Question

Hello,

I have a small issue with performing a simple addition of a certain number of cells in the same column using a variable.

The number of cells may vary with each execution of the macro.

For example:

I want to sum from cell D2 to cell Dx (where x is my variable) and have the total in cell Dx +1.

At the beginning of my macro, I initialize the variable but after that, I can't seem to reuse that same variable in the sum function that I want to perform.

Thank you for your help...

Configuration: Windows XP Internet Explorer 6.0

melanie1324 · Answer

Hello,  sub sum dim sum,line as string sum = 0 line = 2 do while cells(line, 5) <> "" sum = sum + cells(line, 5) line = line + 1 loop  cells(line, 5) = 5  end sub  See you later

Bidouilleu_R · Answer

a small correction

a string variable that adds up gives 2 + 2 = 22
and in integer 2 + 2 = 4
; )
the rest is ok

Sub somme() Dim somme As Double Dim ligne As Integer Dim colonne As Integer somme = 0 ligne = 2 colonne = 5 Do While Cells(ligne, colonne) <> "" somme = somme + Cells(ligne, colonne) ligne = ligne + 1 Loop Cells(ligne, colonne) = somme End Sub

DEN91 · Answer

this solution is not what I'm looking for because it gives me the number of cells while I want the sum of the values contained in those cells...  sorry...

michel_m · Answer

Hello, I didn’t quite understand your request, but if it’s about summing the values of a number of adjacent cells starting from D2, there’s no need to use a loop.

Sub add() Dim lastRow As Long lastRow = Range("D65536").End(xlUp).Row Cells(lastRow + 1, 4) = Application.Sum(Range("D2:D" & lastRow)) End Sub

--
:-x

Bidouilleu_R · Answer

No way!
Melanie1324 made a small mistake, but her code is still correct!

If you use it as it is

put a series of numbers between E2 and E5, for example, 8 9 1 4, the result in E6 will be 22.

But if you want the D column, then change the column value to column = 4.

I'm waiting for your test. And so is Melanie1324.
And if it's good, you put resolved on the first post.

DEN91 · Answer

Thank you for all these solutions, but unfortunately, they do not meet my needs... By adapting the first solution, I can achieve what I want, but my issue is that there is no data present in the cells. The data will be entered later to control the ones from an extraction. I want the total of the cells to be calculated only in the last cell of the column (D22 or D5 or D32 depending on the data present in the extraction) and that this calculation is visible and stays in that cell. For example: ActiveCell.FormulaR1C1 = "=SUM(R[-498]C:R[-1]C)" except that the -498 is replaced by my variable.
I hope this explanation is a bit clearer.

Thanks again to everyone for your help...

DEN91 · Answer

ActiveCell.FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"

melanie1324 · Answer

Hello again,

what you're asking us, I don't know how to do via a macro.

I propose two solutions, either you keep the macro I wrote with the correction from Bidouilleu:
and you click on the macro as soon as you have copied your extraction.

Otherwise, I have another solution.
You reserve the first line and write:
=SUM(D2:D10000)

This way, you'll always have the sum whether there are data or not.

But otherwise, I'm out of ideas.

DEN91 · Answer

No problem ... I'll keep my current macro in the hope of finding out if it's possible. Thank you for your help and I wish you a happy holiday season ... :-) See you!

Loumie64 · Answer

Hello,
I'm a bit late, I don't know if this is what you wanted:

Sub add()
Dim lastRow As Long
Dim lastCol As Long

lastRow = Range("D3").End(xlUp).Row
lastCol = Range("D2").End(xlToRight).Column

Cells(lastRow + 1, 4) = Application.Sum(Range(Cells(2, 4), Cells(2, lastCol)))

End Sub

a+

Sum with variable in VBA macro

10 answers

Suspected virus

Is the autodoc app worth it?

Audio processing object apo update failed?

"windows security" is preventing me from sorting my photos.

Update with windows update (issue)

2,400 km achieved on a single tank in a 1998 car

Hi8 tape recovery with august

Blocking nas freebox pro access on certain pcs.

Site inaccessible

Brother mfc-j6510dw error 50 or 52