Copy Total to the following row in Column A

  • Thread starter Thread starter dd
  • Start date Start date
D

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.
 
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.
 
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
 
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.
 
Back
Top