Need to add values...maybe

R

Ron

A1 24
A2
A3
A4 12
A5
A6 23
A7
A8
I need to go down a column and add the values that are 2 cells apart, like
A4 and A6. There may be up to 7 or 8 in a row....A4, A6, A8, A10, A12, A14,
etc. I'd like to put the total in the first cell (A4), and delete the other
values..in A6, etc, but I'd settle for any solution. I am so tired of
browsing posts for this. They usually get me through, but not this time.

Thanks for any solutions. You guys are amazing.

Ron
 
R

r

Sub test()
s [a1]
End Sub

Sub s(rng As Excel.Range)
Dim l As Long, t, v
Dim rngR As Excel.Range
t = rng.EntireColumn.Cells(1)
For l = 2 To rng.EntireColumn.Cells.Count
If Not IsEmpty(rng.EntireColumn.Cells(l)) Then
Set rngR = rng.EntireColumn.Cells(l)
Exit For
End If
Next
v = Application.WorksheetFunction.Sum(rng.EntireColumn)
rng.EntireColumn.ClearContents
rngR.Value = v - t
rng.EntireColumn.Cells(1) = t

End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html
 
D

Don Guillett

A4 and a6 have only one blank row but try this idea
Sub countifspaces()
lr = Cells(Rows.Count, "a").End(xlUp).Row - 1
For Each c In Range("a1:a" & lr)
If c.Offset(1) = "" And _
c.Offset(2) = "" Then ms = ms + c
Next c
MsgBox ms
End Sub
 
R

Ron

The first one deleted eveything in my column.

It looked like yours was stepping through, until it actually had to add
something.

The data in the cells is time in general format. Should add fine.

Again, I may go the entire file with no cells to add. (2 rows apart). And
then there may be 6 in a row that are 2 rows apart.
A1 24
A2
A3
A4 12 (need to add A4, A6, A8, A10 in this case) Would like the sum of all 4
in
A5 A4, if possible, with A6, A8, A10 cleared.
A6 23
A7
A8 11
A9
A10 14
A11
A12
A13
A14

Thanks for all the efforts.

Ron
 
D

Don Guillett

I'm not sure that the last row qualifies but try this to get 60 from your
sample.

Sub countifspaces1()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
If c.Offset(2) > 0 Then ms = ms + c
Next c
MsgBox ms + Range("a" & lr)
End Sub
 
R

Ron

Don,

I'm getting close. I'm adding the values, when the offset is 2, but how do I
clear the cell from the first value and write the sum in the second cell, or
a cell next to it?

If A4 and A6 have values, then I want to clear A4 and write the sum in A7
(or I'd settle for B6 (clearing both A4 and A6).

And if there are more, then I need to sum the next offset 2 value, again
clearing the last one...and if I wrote the sum in B7, then that would need to
be cleared also.

I'm just not good at storing and writing cell values.

Thanks,

Ron
 
D

Don Guillett

I'm having a hard time trying to visualize what you want. Send your file to
my address below along with this msg and before/after examples.
 
R

Ron

Don,

With all the help I did get, I got it figured out. Clutzy, but works. Steps
down and if active call and offset 2 is not null, it goes to offset 2, adds
tham and then goes back to offset -2 and clears it and starts stepping again.
I didn't know how to clear the offset -2 from where I was, but it works....I
really do appreciate you guys, because this was put together from about 5
posts...

Ron

Sub Macro5_Adds_calls()


' Adds calls with hold together.
Range("O2").Select
TotalRows = ActiveCell.CurrentRegion.Rows.Count
For StartRun = 1 To TotalRows
If ActiveCell.Value <> "" Then
If ActiveCell.Offset(2) <> "" Then
ActiveCell.Offset(2).Select
ActiveCell.Value = ActiveCell.Value + ActiveCell.Offset(-2)
ActiveCell.Offset(-2).Select
ActiveCell.Clear
ActiveCell.Offset(1).Select
Else
ActiveCell.Offset(1).Select
End If
Else
ActiveCell.Offset(1).Select
End If
Next StartRun
Range("A1").Select
End Sub
 
R

Ron

That's ok

With all the help I did get, I got it figured out. Clutzy, but works. Steps
down and if active call and offset 2 is not null, it goes to offset 2, adds
tham and then goes back to offset -2 and clears it and starts stepping again.
I didn't know how to clear the offset -2 from where I was, but it works....I
really do appreciate you guys, because this was put together from about 5
posts...

Ron

Sub Macro5_Adds_calls()


' Adds calls with hold together.
Range("O2").Select
TotalRows = ActiveCell.CurrentRegion.Rows.Count
For StartRun = 1 To TotalRows
If ActiveCell.Value <> "" Then
If ActiveCell.Offset(2) <> "" Then
ActiveCell.Offset(2).Select
ActiveCell.Value = ActiveCell.Value + ActiveCell.Offset(-2)
ActiveCell.Offset(-2).Select
ActiveCell.Clear
ActiveCell.Offset(1).Select
Else
ActiveCell.Offset(1).Select
End If
Else
ActiveCell.Offset(1).Select
End If
Next StartRun
Range("A1").Select
End Sub
 
D

Don Guillett

Testing your code with the sample provided resulted in 60 in o10. This does
the same without selections.

Sub Macro5_Adds_callsSAS1()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "o").End(xlUp).Row
ms = Cells(lr, "o")
For i = lr To 4 Step -1
If Cells(i - 2, "o") > 0 Then ms = ms + Cells(i - 2, "o")
Next i
Cells(lr, "o") = ms
Cells(2, "o").Resize(lr - 2).Clear
Application.ScreenUpdating = False
End Sub
or
Sub Macro5_Adds_callsSAS()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "o").End(xlUp).Row
For Each c In Range("o2:blush:" & lr)
If c.Offset(2) > 0 Then ms = ms + c
Next c
Range("o" & lr) = ms + Range("o" & lr)
Range("o2").Resize(lr - 2).Clear
Application.ScreenUpdating = True
End Sub
 
R

Ron

Thanks Don,

I only really started getting into Macros and VB a couple of months ago, so
there is a lot of syntax I don't know. I appreciate when you advanced guys
help out. I'll add your new code to my mix and see what happens.

I noticed, after all that, I'm missing a type of call that does not follow
the offest 2 rule, so now I may have to store phone numbers from a different
column and check them when I get the time. Same phone number, same call.
That's down the road.

Thanks for all your help.

Ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove specific cells in a column. 2
How to delete ALL cells containing the same data 1
Which zones are touch given this range 2
how to use an formula to transpose a list(left) to be like the right? 2
Need macro that concatenates cells with text 7
Average last seven if not zero 5
copying cells at a specific interval 2
Move data from first 7 rows from one column A into multiple Columnsthen the next 7 rows 2

Top