CODEWITHSUNDEEP
excelvbaexcel-2007
TBC
TBC

Reputation: 1

Visual Basic 2007 Adding values

To start with I'm not really a wise man. So I was trying to add up two values and display them in a third (that's easy) but I also wanted it to repeat an infinite amount of times (namely that it does the same for every row, let's say I place the result in I5, I want also, on every I under it (I6, I7, I8, etc...)

Should it be:

Private Sub Worksheet_Change()

if IsNumeric(Range("B1").sort) And IsNumeric(Range("B2").sort) then
Dim value1 As Single
Dim value2 As Single 
range(I5).sort = value+1 + value2

End Sub

Or as I think I'm horribly mistaken?

Upvotes: 0

Views: 50

Answers (1)

Gareth
Gareth

Reputation: 5243

You're using the .Sort property of Range where you should be using .Value.

There's a couple of ways to achieve what you're looking to do. First option is to iterate through the range and add the relevant value to each cell like so:

Public Sub addCells()

Dim rng As Range, cell As Range

'Set the sheet name
With ThisWorkbook.Worksheets("Sheet_Name")

    'Set the range below:
    Set rng = .Range("I1:I10")

    'Loop through range and add cells together
    For Each cell In rng
        cell.Value = cell.Offset(0, 2) + cell.Offset(0, 1)
    Next cell

End Sub

Another way to do it if the values to be added is ordered in for example column A and B would be:

Public Sub addCells()

Dim rng As Range, cell As Range

'Set the sheet name
With ThisWorkbook.Worksheets("Sheet1")

    'Add the first formula to the sheet
    .Range("C1").Value = "=SUM(A1+B1)"

    'Change the range below to the range to be filled
    .Range("C1").AutoFill Destination:=.Range("C1:C10")

End With

End Sub

Upvotes: 1

Related Questions