PDA

View Full Version : For in columns Range



tomzko
12-30-2015, 02:51 PM
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


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 :)

SamT
12-30-2015, 05:51 PM
this will work if there are no numbers below ActiveSheet.Range("[B to E]1").End(xlDown)

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)

Set rngSuma = rngSuma.Offset(0, 1)