Help vba loop

[eddg2011]

Hello bois,

I need your help once more. I'm putting together a Macro to find a certain string, then add the string's row and the one above it together. This string is on many rows, but not on different columns.

All I have is this, finds the string then grabs the cells needed to add them together, but I need the whole row. Columns are from Column C to Column CO. Any ideas on how to get this to work?

Sub E()
Dim Pub As Range, Col As Range, Ad As Range
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
  With Sh.UsedRange
        Set Pub = .Cells.Find(What:="Printing and Publications")
        If Not Pub Is Nothing Then
            Do Until Pub Is Nothing
                Set Ad = Pub.Offset(-1, 1)
                Set Col = Pub.Offset(0, 1)
                'Pub.Offset(1).EntireRow.Insert shift:=xlDown /inserts a new row
                Pub.Value = Ad + Col
                Set Pub = .FindNext(Pub)
            Loop
        End If
  End With
  Set Pub = Nothing
Next
End Sub

 

[eddg2011]

Sub E()
Dim Pub As Range, Col As Range, Ad As Range, X As Range
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
  With Sh.UsedRange
        Set Pub = .Cells.Find(What:="Printing and Publications")
        If Not Pub Is Nothing Then
            Do Until Pub Is Nothing
                
                Set Ad = Pub.Offset(-1, 1)
                Set Col = Pub.Offset(0, 1)
                Set X = Pub.Offset(0, 1)
                X.Value = Ad + Col
                Pub.Value = "Communications"
                Set Pub = .FindNext(Pub)
            Loop
        End If
  End With
  Set Pub = Nothing
Next
End Sub

Something like this. But now run a loop side ways to get all the row's columns

 

[stephanfb]

I am trying to have a function that calculates de Variance of returns (ln(return2/return1)^2) of the S&P in order to have it cumulatively added and subtract it from a "budget" number.
Once this substraction becomes equal or larger than "budget" I want it to provide me the Date in which day that happens ( on column A)
So it looks like this
Column A Column B Column C
01/30/2015. 2550 01/29/2015. 2600 ....... ... 04/25/2014. ...
I have written the following code but I fail to obtain the desired result:
Public Function strange_sum(inrange As range, budget As Double) As Long
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-style: inherit; font-variant-caps: inherit; font-stretch: inherit; line-height: inherit; vertical-align: baseline; white-space: inherit;"> Dim total As Long
Dim i As Double
Dim w As Double

i = 8
total = 0

While total < budget
i = i + 1
var = Log(Cells(i, 8).Value / Cells(i + 1, 8).Value)
total = total + var

If i = 2 And total < 0 Then total = 0



strange_sum = Cells(i, 9).Offset(0, -1)

Wend
</code>End Function
I need this for work :/ id appreciate all your help.
Thanks! ( its my first post)