CODEWITHSUNDEEP
excelvba
sublime19
sublime19

Reputation: 1

Append text to a found cell

I have multiple spreadsheets with a hours as text in Column A and and a running sum total beside in in Column B.

The row in which we have the hours listed varies which is why I run a find to get its location.

We run a rolling sum (=5+3+6+8) to have a visual cue as in, the individual worked out 5 hours the first day, 3 hours the next, 6 the next etc.

I'm trying to build a formula that would be able to add to that string and complete the formula so I would see the total of the formula in the spreadsheet and the individual additions in its contents.

So far I have something to find the cell and locate it but I can't get it to append to the end in its contents.

Consider the thours variable,

Sub findhours()
 Dim hours As Range
    Set hours =     Application.Workbooks("vbstest.dec17").Worksheets("sheet1").Cells.Find("hours")
 Dim thours As Range
    Set thours =     Application.Workbooks("vbstest.dec17").Worksheets("sheet2").Cells.Find("hours")

 Dim z As Integer
  Dim zh As Integer

    z = hours.Column
    zh = thours.Column

Dim x As Integer
Dim xh As Integer

    x = hours.Row
    xh = thours.Row


   Application.Workbooks("vbstest.dec17").Worksheets("sheet1").Cells(x, z).Offset(0, 1).Value = 5

The above satisfies hours for daily, by thours needs a little extra. How do i take something like:

Range(zh, xh + 1)

which is a string of addition (=5+3+6+8) and be able to add to that addition to get something like (=5+3+6+8+4) and then be able to return to this macro to keep adding to this formula? Most of my attempts have returned its value (22).

Thanks for all your help.

Upvotes: 0

Views: 100

Answers (1)

YowE3K
YowE3K

Reputation: 23994

I can't quite figure out which cell contains what in your question but, assuming that Cells(r1, c1).Value contains the current total hours worked, and Cells(r2, c2).Formula contains a formula that calculated the previous total hours worked, then you can update the formula to reflect the current hours worked by something like:

Cells(r2, c2).Formula = Cells(r2, c2).Formula & "+" & _
                        (Cells(r1, c1).Value - Cells(r2, c2).Value)

I think I have worked out your sheet layout, so I think the formula becomes

With Application.Workbooks("vbstest.dec17").Worksheets("sheet2")
  .Cells(xh, zh + 1).Formula = .Cells(xh, zh + 1).Formula & "+" & _
     (Application.Workbooks("vbstest.dec17").Worksheets("sheet1").Cells(x, z + 1).Value - _
     .Cells(xh, zh + 1).Value)
End With

Upvotes: 1

Related Questions