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.
|
Similar Threads
