Macro to insert data in rows containing text

Microsoft Office excel 2007 home & stude...
September 30, 2010 at 08:48:29
Specs: Windows XP, 2gb
I have a spreadsheet starting in col F row 148 with data to col O, and with a number of rows that change from week to week (say 100 average). Because of subtotal function, sporadically, that function inserts a row with the text in cell F that consists of a combination of the cell number above it and the word Total (nnnnnn Total). I need to find each of these subtotals and replace the contents in Col F with a formula that equals the cell above it so it becomes just nnnnnnn (if in row 155, it would be =F154), in col G, I need it to insert the text, "PER DIEM", in in column O, I need to insert the formula (again assuming row 155) =P155. All of this will occur on each row containing the text with a number that varies plus the word "Total". If it is easier, another trigger is in the row following the inserted subtotal row, if col is <>0, the the changes occur in the preceding row.

See More: Macro to insert data in rows containing text

Report •

September 30, 2010 at 08:56:19
Thanks for telling us what you need.

If you'd like some help, feel free to ask.

Report •

September 30, 2010 at 09:23:05
Thanks for setting me straight. I certainly would like some help. Just reading these have helped a great deal, but I find nothing that fits my situation exactly. Thanks for asking.

Report •

September 30, 2010 at 09:26:46

I have very little knowledge of Macros. I have been trying to write one on my on that will accomplish the goal I outlined, but to no avail.

Report •

Related Solutions

October 1, 2010 at 08:30:08

I thought when you followed up so promptly, that you were willing to help me, but in reading back my response, I see where you could have mistaken my reply as being a little snide. I am sorry. i did not mean it that way - if, indeed, you took it that way. I really, really need help solving this problem with a macro. If it is not something you want to tackle, I understand. Thanks anyway for your original interest.

Report •

October 1, 2010 at 09:20:18
All assistance offered here is done on a voluntary basis, as time allows.

You may get a prompt response if someone is logged in and checking and then you may not get another response for a while as we deal with things in our "real life".

I will look at your question and see if I can offer some suggestions.

Report •

October 1, 2010 at 12:22:10
Since I can't see your spreadsheet from where I am sitting, I had to take a best guess on the layout.

Try this code in a backup copy of your workbook since macros cannot be undone.

Size the VBA window so that you can see your data behind it, place your cursor anywhere in the code and press F8 repeatedly to Single Step through the code.

As soon as it finds the first cell with "Total" in Column F, you should be able to tell if it's going to do what you want it to do.

Option Explicit
Sub NoTotalText()
Dim lastF_row As Integer
Dim c As Range
Dim firstAddress As String
'Find last Row with data in Column F
 lastF_row = Range("F" & Rows.Count).End(xlUp).Row
'Loop through Column F looking for "Total"
  With ActiveSheet.Range("F1:F" & lastF_row)
    Set c = .Find("Total", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
'If Grand Toal is found, we're done
            If Cells(c.Row, "F") Like "*Grand*" Then Exit Do
'Replace Total with nothing, add data and formula to other columns
             Cells(c.Row, "F").Replace What:="Total", Replacement:=""
             Cells(c.Row, "G") = "PER DIEM"
             Cells(c.Row, "O").Formula = "=P" & c.Row
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub

Report •

Ask Question