Sum Cell with Cell above if Text equals this

[Gusman1966]

Hi all

could you help me with the code to be able to sum two cells if a text is found.

i want to look for text in col A, then sum the figure next to that in col B with the cell above, with the answer in col c as below example.
I'm quite new to vba so if you could explain the logic in simpleton terms it would much appreciated.

i tried to amend a code that deletes rows that i gleened from this forum but my logic is flawed me thinks

 

[par60056]

Welcome to the board.

Your examples did not make it. You cannot post the excel. You can use the advanced editor to type in tables or include a screen shot.

No VBA should be needed.

c2 = "=if(A2="Magic text",B2+B1,"")

 

[Gusman1966]

Hi par60056

thanks for your quick reply.

yes i realise my screenshots didn't make it as i posted. This is my first post.
My nearly finished macro sorts a report into a list, i need the macro to then look for this repeating text "cash A" or "cash B"(50 groups) and sum the cell next with the one above. I will then get the macro to hide the col b to give me the finished report.

example below is 1 group​

HOUSE 1
	2287
Cash A	33887	36174
	7756
Cash B	-4765	2991

<tbody>
</tbody>

 

[par60056]

how about something like this:

Dim lastRow As Long
Dim currRow As Long
Dim matchRow As Long


lastRow = ActiveSheet.UsedRange.Rows.Count


matchRow = 2
For currRow = 2 To lastRow
    If (Cells(currRow, 1) <> "") Then
        Cells(currRow, 3) = "=sum(""B" & matchRow & ":B" & currRow & """)"
        matchRow = currRow + 1
    End If
Next

 

[Gusman1966]

Many thanks,

your solution worked. although it gave a formula which gave a "#value" error as the formula had comma on the cell, i.e =sum("B3:B4") any idea what caused that?
With help i tweaked your suggestion to give figure without the formula which for this will work better as i want to delete column B at the end.
The code also tried to sum the group name (House 1) which isn't a big issue but ideally it would be good not to. I know i could put code to return "nothing" if 0 .
Is there a way to just look for the text then go from there, because it will be one of the two in the example?
Also the last two rows didn't calculate any idea why?

I think i need help on how to insert screen prints and code, is there a forum for that?
The insert image asks for a web link?
How did you insert your code example

Sorry for so many questions but your help is really appreciated.
How is Chicago?

 

[par60056]

I'm not sure why I made it put quotes around the range going to sum.

The line :
Cells(currRow, 3) = "=sum(""B" & matchRow & ":B" & currRow & """)"


should be:
Cells(currRow, 3) = "=sum("B" & matchRow & ":B" & currRow & ")"

This will make the formula =sum(B3:B4) instead of =sum("B3:B4").

Not knowing exactly what you are trying to do I was trying to give you as general an example as possible.

If there are only ever 2 values to add together and you plan to eliminate column B so you don't want a formula, you might want to change that line to :
Cells(currRow, 3) = cells(currRow,2)+cells(CurrRow-1,2)

if you only want to add it on rows where A contains "Cash..." then the line:
If (Cells(currRow, 1) <> "") Then

becomes:
If (left(Cells(currRow, 1),4) = "Cash") Then




When editing the message to post, there are some special tags you can use. You put them in square brackets and then it knows what to do. The only 2 I have used are CODE /CODE and Quote /Quote.

 

[Gusman1966]

Hi par60056

Your help as been superb. I amended your suggestion as below, so it now looks for the text "Cash A" in Col A, sums the two row in Col B with the answer in C. Which is what i needed it to do.
The last part which would finish this off is need to do this for "Cash A" and for "Cash B". Currently to get the results i need i had to do two separate scripts to make it happen. I tried to just copy the main bit to run both in one script but it won't work. i know its probably something simple but could you point me in the right direction.

Once again your help has been tremendous.

Many thanks


Dim lastRow As Long
Dim currRow As Long
Dim matchRow As Long




lastRow = ActiveSheet.UsedRange.Rows.Count




matchRow = 2
For currRow = 2 To lastRow
If (Cells(currRow, 1) <> "Cash A") Then
Cells(currRow, 3).Value = Cells(currRow, 2).Value + Cells(currRow - 1, 2).Value
matchRow = currRow + 1
End If
Next
End Sub

 

[par60056]

Your IF statement says to put the value in column C when Column A is NOT "Cash A"

If you look near the bottom of my last post I said:

if you only want to add it on rows where A contains "Cash..." then the line:
If (Cells(currRow, 1) <> "") Then

becomes:
If (left(Cells(currRow, 1),4) = "Cash") Then

This will look for the first 4 characters of the value in column A to be "Cash" so it will execute for "Cash A","Cash B","Cash C","Cashew" or "Cashier".

 

[Gusman1966]

Yes apologies that was misleading. I had put the = in my work version but not in the example above.

That solution has worked perfectly, many thanks