-
For in columns Range
Hi everyone, im learning the basic of VBA and i can figure out how to advance in cell range.
What i need in my Macro is: From a Worksheet (ex: W1) select a whole cell, and the result paste it in another worksheet (W2) cell (A1), then pick another column of W1 and sum it and paste it in A2 and so on.
What i've wrote is
Code:
Sub SumasTbf()
Dim Seleccion As Range
Dim Suma
Worksheets("hoja2").Activate
Set Seleccion = Range(ActiveSheet.Range("B1"), ActiveSheet.Range("B1").End(xlDown))
Suma = WorksheetFunction.Sum(Seleccion)
Worksheets("hoja3").Range("F1") = Suma
End Sub
Now im thinking in using a FOR to move the range B1 to C1 but i dont know how to move the B to C, can you help me plz! in case of working with rows it would be "B"& x ?? (another question)... waiting for an answer :)
-
this will work if there are no numbers below ActiveSheet.Range("[B to E]1").End(xlDown)
Code:
Dim rnbSuma As Range
Set rngSuma = Worksheets("hoja3").Range("F1")
For columna = 2 to 5 'Column B to Column E
rngSuma.Value = WorksheetFunction.Sum(Worksheets("hoja2").Columns(columna))
Set rngSuma = rngSuma.Offset(1)
Next columna
Set Seleccion = Range(ActiveSheet.Range("B1"), ActiveSheet.Range("B1").End(xlDown))
This is the same, but for any column number (columna)
Set Seleccion = Range(ActiveSheet.Cells(1, columna), ActiveSheet.Cells(1, columna).End(xlDown))
If you desire to move rngSuma to the right across columns, (F1 to G1 to H1 ...) then use .Offset(0 Rows, 1 columns)
Code:
Set rngSuma = rngSuma.Offset(0, 1)