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.

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 wrote:
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.

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 wrote:
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 wrote:
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.

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.

dd wrote:
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 wrote:
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 wrote:
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.