Copy Total to the following row in Column A

[dd]

Hi, I'm working on a list of vendor names and the total in Column A.
For example:

Rows
1 Name A
2 Name B
3 Name C
4 Total Spend - Cars
5

I want row 5 to copy row 4 but it would display "Total N Spend - Cars"
So for each row that contains "Total Spend" copy it to the following
row and add the name N between Total and Spend. So another example
would be if row 8 show, Total Spend - Apples, then row 9 would display
Total N Spend - Apples. I'm still very new to these macro codes. Any
help would greatly be appreciated. Thanks so much.

 

[Rawce]

Many ways of doing it, but the one I'm most familiar with is Looping.
Note, this assumes that every cell above is populated. If there's a
chance that you won't start from Row 1 or there may be blanks, let me
know and I'll code it more robustly.

Sub TotalSpend()

Dim iCount As Integer
Dim iMax As Integer
Dim iLength As Integer

iCount = 1
iMax = WorksheetFunction.CountA(Sheets("Sheet1").Columns(1))

Do Until Left(Sheets("Sheet1").Cells(iCount, 1).Value, 11) = "Total
Spend"
iCount = iCount + 1
Select Case iCount
Case Is > iMax
MsgBox "Some data must be missing in Column A of Sheet1."
Exit Sub
End Select
Loop

iLength = Len(Sheets("Sheet1").Cells(iCount, 1).Value)

Sheets("Sheet1").Cells(iCount + 1, 1).Value = "Total N Spend - " & _
Right(Sheets("Sheet1").Cells(iCount, 1).Value, iLength - 14)

End Sub

Sure you want just an N in there? Could easily put in the number of
entries above the first instance of Total Spend.

Hope this helps,

Ross.

 

[dd]

Hi Ross,

My data starts on row #6 and ends on row 3200. I highlighted all the
data in column A and there are no spaces in between, then I ran the
code you provided. But a "compile error was encounter, sub or function
not defined." Not sure what to do. Also the N would be used for a name
I would be putting in later.
Thanks for your help.
dd

 

[Rawce]

Sorry, was away over Christmas and New Year without much Internet
access.

Does the compile error point to this line:
Do Until Left(Sheets("Sheet1").Cells(iCount, 1).Value, 11) = "Total
Spend"

If so, all that should be on a single line as the text was wrapped by
the Groups text window. If not, let me know and I'll have a look.

I think you can start from Row 6 by changing iCount = 1 to iCount = 6.

Let me know how you get on, and sorry again for the delay.

Cheers,

Ross.