Rawce
12/19/2006 7:41:00 PM
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.