Trying Post Document

[gzell]

I am researching my Great Great Grandfathers time during the Civil War. What started to be a simple Excel file has grown to a large file, 25000+ rows. Not what I planned for but the further I got in the research the more I expanded to included all members of his units. I am new to VBA and have am trying to learn the basics to complete this document. If this request is not allowed please let me know.

Flow
Search Column D for Cell "Rank:"
When found, insert row below this Cell
Also, insert text "Age:" in column D in new row
Insert row below Cell with "Age"
insert "Residence:" in column D of this row
Loop through Range of Spreadsheet
Repeat
Until end of document
End

This is a code I found online, to enter one line but it does not work as expected. Any help would be greatly appreciated.

Sub Insert_Row_Below_Blank()
Dim i As Range
Dim cell As Range
Set i = Range("D1:D25391")
For Each cell In i.Cells
If cell.Value = "Rank:" Then
cell.Offset(1).EntireRow.Insert

End If
Next
End Sub







Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Document ID	Person_ID	Name	Details	Notes
2			xxxxxx
3			xxxxxx	Service:
4			xxxxxx	Location:
5			xxxxxx	Regiment:
6			xxxxxx	Function:
7			xxxxxx	Company:
8			xxxxxx	Rank:
9			xxxxxx	Enrolled:
10			xxxxxx	Date:
11			xxxxxx	Enlisted:
12			xxxxxx	Date:
13			xxxxxx	Detail:
14			xxxxxx	Captured:
15			xxxxxx	Prisoner:
16			xxxxxx	Died:
17			xxxxxx	Cause:
18			xxxxxx	Buried:
19			mmmm
20			mmmm	Service:
21			mmmm	Location:
22			mmmm	Regiment:
23			mmmm	Function:
24			mmmm	Company:
25			mmmm	Rank:
26			mmmm	Enrolled:
27			mmmm	Date:
28			mmmm	Enlisted:
29			mmmm	Date:
30			mmmm	Detail:
31			mmmm	Captured:
32			mmmm	Prisoner:
33			mmmm	Exchanged:
34			mmmm	Location:
35			mmmm	Battle:
36			mmmm	Date:
37			mmmm	Detail:
38			mmmm	Location:
39			mmmm	Date:
40			mmmm	Captured:
41			mmmm	Forwarded:
42			mmmm	Date:
43			mmmm	Date:
44			mmmm	Location
45			mmmm	Died:
46			mmmm	Cause:
47			mmmm	Buried:

<tbody>
</tbody>

Sheet1​

 

[gzell]

My mistake it goes to End Sub after placing text in Row 97.

 

[Michael M]

If you step through the code using F8, hover your mouse over lr once you get to the line after lr=, and tell us what it returns.
Also at rows 96 / 97 do you have merged cells

 

[gzell]

In the Rows with Rank: they would also have the Rank designation of the Soldier. I removed the designation at the bottom of sheet and it worked on that entry. I do not want you to spend a lot of your time on this. I really appreciate the help. There are 3000+ entries for Rank so I might just replace the entries to Rank: and correct after we get this working. Is there a notation in the command that would tell it to look for just Rank: in the row? If not I can make this work. Thanks again for the response.

 

[Fluff]

What are the ranks that it ignores?

 

[gzell]

It ignores ranks with any text the space after the : I changed the all the entries to Rank: and it worked. This is all done on a test worksheet. The code Fluff posted worked up to the point when it added row and text on row 96 & 97 and then it went to end. It started from top instead of bottom, as he mentioned in his last post.

 

[Fluff]

If you step through the code using F8 & stop when the row in red is highlighted yellow

Sub gzell()
   Dim Ar As Areas
   Dim Rng As Range
   With Range("D2", Range("D" & Rows.Count).End(xlUp))
      .Replace "Rank:", "=xxxRank", xlPart, , False, , False, False
      [COLOR=#ff0000]Set Ar = .SpecialCells(xlFormulas, xlErrors).Areas[/COLOR]
      .Replace "=xxxRank", "Rank:", xlPart, , False, , False, False
   End With
   For Each Rng In Ar
      Rng.Offset(1).EntireRow.Resize(2).Insert
      Rng.Offset(1).Resize(2).Value = Application.Transpose(Array("Age:", "Residence:"))
   Next Rng
End Sub

You should see cells with #NAME? in them. Do you also see cells with Rank: as well?

 

[gzell]

I see #Name in the first 5 rank cells. When I click on the #Name ---=xxxRank Private. shows in the far left of the cell. The code stops at same place it did before. When I step through the remainder of the code the Rank field is replaced with the correct information. This continues to the same rank cell as before and then goes to End Sub.

 

[Fluff]

When you step through the code to the point that the cells show #NAME?
Are there any cells that still show Rank: rather the the name error?

 

[gzell]

Rank: is in all cells below the last successful change. It is the same location it stopped previously. When stepping through the code, when it makes that change, it jumps to End Sub.

 

[Fluff]

What else is in those cells?