Create a code vba to collect the value of a specific cell from two different columns and put the result in another column

[Dayski147]

I want to calculate the sum of each cell from the first column with the corresponding cell in the second column and put the result in the third column....and so only the end of the column

Example:
code vba:
Range ("J11")=SIERREUR(SOMME(H11:I11)/2;"")
Range ("J12")=SIERREUR(SOMME(H12:I12)/2;"")
Range ("J13")=SIERREUR(SOMME(H13:I13)/2;"")

And in the case that the column cells are empty, it does not put the formula
to the end of the column

Please perform the same task on all colored columns

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Jamie McMillan]

Hello Dayski,

The code below will paste the mean average in the columns you wish.

Sub AverageColumns()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
j = 8
k = 9
l = 10

    Do Until j > 47
    For i = 11 To 55
    If (Cells(i, j).Value + Cells(i, k).Value) / 2 = 0 Then
    Cells(i, l).Value = ""
    Else
    Cells(i, l).Value = (Cells(i, j).Value + Cells(i, k).Value) / 2
    End If
    Next i
    j = j + 3
    k = k + 3
    l = l + 3
    Loop
End Sub

Using your range A11:AW55

Jamie McMillan

 

[Dayski147]

Hello Dayski,

The code below will paste the mean average in the columns you wish.

Sub AverageColumns()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
j = 8
k = 9
l = 10

    Do Until j > 47
    For i = 11 To 55
    If (Cells(i, j).Value + Cells(i, k).Value) / 2 = 0 Then
    Cells(i, l).Value = ""
    Else
    Cells(i, l).Value = (Cells(i, j).Value + Cells(i, k).Value) / 2
    End If
    Next i
    j = j + 3
    k = k + 3
    l = l + 3
    Loop
End Sub

Using your range A11:AW55

Jamie McMillan

Thank you very much.

 

[Jamie McMillan]

Thank you very much.

Hello Dayski,

Can you mark it as a solution; so that nobody spends their time solving it again.

Jamie McMillan

 

[Dayski147]

Hello Dayski,

Can you mark it as a solution; so that nobody spends their time solving it again.

Jamie McMillan

Really, thank you for an hour and a half and I've been trying to no avail

 

[bebo021999]

Alternative solution, avoid loop, may help the code faster

Sub test()
For i = 8 To 49 Step 3
    Set u = Range(Cells(11, i + 2), Cells(55, i + 2))
    u.Value = Evaluate("=(" & Range(Cells(11, i), Cells(55, i)).Address & "+ " & Range(Cells(11, i + 1), Cells(55, i + 1)).Address & ")/2")
Next
End Sub

 

[Dayski147]

Alternative solution, avoid loop, may help the code faster

Sub test()
For i = 8 To 49 Step 3
    Set u = Range(Cells(11, i + 2), Cells(55, i + 2))
    u.Value = Evaluate("=(" & Range(Cells(11, i), Cells(55, i)).Address & "+ " & Range(Cells(11, i + 1), Cells(55, i + 1)).Address & ")/2")
Next
End Sub

Thank you. A question please

Thank you, I was looking for a code to empty the same columns on the sheet

 

[Dayski147]

Thank you. A question please

Thank you, I was looking for a code to empty the same columns on the sheet

Sub clr()

Dim x As Long

'Get lastrow of data in Col A
Dim lastrow As Long
lastrow = Range("J" & Rows.Count).End(xlUp).Row
'
'Delete until done
For x = 11 To lastrow
Range("j" & x & ":m" & x & ",p" & x & ",s" & x & ":v" & x & ",y" & x & ",Ab" & x & ":Ae" & x & ",Ah" & x & ",Ak" & x & ":An" & x & ",Aq" & x & ",at" & x & ":aw" & x).ClearContents

Next x
End Sub

 

[Dayski147]

Thank you. A question please

Thank you, I was looking for a code to empty the same columns on the sheet

Sub clr()

Dim x As Long

'Get lastrow of data in Col A
Dim lastrow As Long
lastrow = Range("J" & Rows.Count).End(xlUp).Row
'
'Delete until done
For x = 11 To lastrow
Range("j" & x & ":m" & x & ",p" & x & ",s" & x & ":v" & x & ",y" & x & ",Ab" & x & ":Ae" & x & ",Ah" & x & ",Ak" & x & ":An" & x & ",Aq" & x & ",at" & x & ":aw" & x).ClearContents

Next x
End Sub

But it's kinda slow

 

[Jamie McMillan]

Hello Dayski,

Just change the code that inputs the mean average:-

As below

Sub DeleteColumns()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
j = 8
k = 9
l = 10
    Do Until j > 47
    For i = 11 To 55
    Cells(i, l).Value = ""
    Next i
    j = j + 3
    k = k + 3
    l = l + 3
    Loop
End Sub

Jamie McMillan