Solved: Find/Replace in One Column over Entire Workbook

[Chisholm]

Hi,
I'm trying to find and replace on only a single column in an entire workbook.
I'd like to replace all blanks with a character or string (for search purposes).
I've been trying to modify the following code:

Sub ReplaceText()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c = Replace(c, "TextToFind", "TextToReplace")
Next
End Sub

I've tried changing the active sheet to activeworkbook, but that won't work.

-Chase

[grichey]

This will prompt you to enter a range so it doesn't replace the entire column with whatever it is you're wanting to put in place of blank.
[VBA]Option Explicit
Sub ReplaceBlanks()
Dim myRange As Range
Dim x As Range
Set myRange = Application.InputBox("Range?", "Range?", Type:=8)
For Each x In myRange
If x = "" Then x = "NoLongerBlank" 'Put your replacing string here
Next x
End Sub[/VBA]

[Chisholm]

I guess that I'm having trouble getting my range to be the same column on every worksheet in the book. There are 30+ sheets, so I'd prefer to not do this manually each time.
For example, I want to change only column F on all sheets in the workbook.
How do I name that range? I've tried using an array per MS's 3D range help, but was not able to get that to work.

Similarly, I would like to get this to only search through one column in all worksheets:
vbaexpress.com/kb/getarticle.php?kb_id=780
"Search all worksheets for word/phrase and list results with hyperlink to cell."

*I'm trying to create a summary sheet that lists all items from all worksheets that contain either a blank or "DNC" in their "status" column (F).

[grichey]

Something like maybe?
[VBA]Option Explicit
Sub ReplaceBlanks()
Dim myRange As Range
Dim x As Range
Dim ws As Worksheet

Set myRange = Application.InputBox("Range?", "Range?", Type:=8)
For Each ws In Worksheets

For Each x In myRange
If x = "" Then x = "NoLongerBlank" 'Put your replacing string here
Next x

Next ws

End Sub[/VBA]

[greymalkin]

If you know what the range will be in each sheet and it seems like it's not always in the same column you may want to set up a name defined range for each sheet (Insert - Name - Define) to make the coding easier and put it in the loop described above. If you create a Range called "Range_1" on the first sheet, "Range_2" on the second sheet, and so on then you could make an integer variable (we'll call it i) in the loop and do something like:

[vba]
Sub TestRanges()

Application.ScreenUpdating = False

Dim i As Integer ' For looping through ranges
Dim j As Integer ' For looping through individual cells in each range
Dim height As Integer 'For capturing the size of each range

' Loop through all Name Defined ranges (assuming 30 ranges, 1 for each sheet)
For i = 1 To 30
Application.Goto "Range_" & i
height = Range("Range_" & i).Count 'gets size of the range

' Loop through all cells in this particular Name Defined range
For j = ActiveCell.Row To ActiveCell.Row + (height - 1)
If ActiveCell.Value = "" Then ActiveCell.Value = "*" ' this will work for cells that are null as well as "".
ActiveCell.Offset(1, 0).Select
Next j

Next i

Application.ScreenUpdating = True

End Sub
[/vba]

Since the name defined ranges contain the Sheet and Cell information there is no need to cycle through each sheet. When you call Application.Goto Range_3 it will automatically select the appropriate range on Sheet3...and so on.

I had to edit my code as I was originally doing a Find/Replace looking for "" and replacing with "*". The issue was that if the cell was null or no data had ever been written to it it didn't see it as "" so I had to complicate the code a bit and repost for the sake of pride . There may be a much more elegant solution but this should work.

[Chisholm]

Thanks,
That worked so far, except now I realized that I don't want it to do anything if the "title" column corresponding to that row is blank.
I tried adding an "AND" with that argument, but that does not seem to work. Here's what I tried, it doesn't seem right to me though:
Option Explicit
Sub ReplaceBlanks()
Dim myRange As Range
Dim x As Range
Dim ws As Worksheet

Set myRange = Application.InputBox("Range?", "Range?", Type:=8)
For Each ws In Worksheets

For Each x In myRange
If x = "" And "B" & Right(x, Len(x) - 1) <> "" Then x = "NoLongerBlank" 'Put your replacing string here
Next x

Next ws

End Sub

End Sub


Thanks again for your patience, I'm an EE student that is getting (self) trained to be the Excel guy at my internship. So, I have a little programming exposure, but mostly Matlab and such...

[grichey]

Thanks,
That worked so far, except now I realized that I don't want it to do anything if the "title" column .

Do you mean if the title cell is blank?? Like if your data was in F then F1 would say status? This forum is good to learn. I had zero vba experience before getting on here and was just looking for a way to make life easier.

[Chisholm]

I apologize for being cornfusing/cornfused...

I'm not quite sure what you are asking me, but here goes:
1. On the form, there are some completely blank lines, I want those to stay as is.
2. I want the line items that do exist but have a blank "Status" cell to say "blank" for their status.

* The reason I want this is so I can search for "blank" and create a summary at the end of the book that shows all items from all books that need their status changed. The use of the term "blank" is arbitrary but seems effective so far.

Thanks,
Chase

[Chisholm]

I forgot to mention that the data is uniform across the book. All Status columns are in "F" and all titles are in "B".

[greymalkin]

now you tell me!

[grichey]

This almost does what you want though it's not skipping your entirely blank rows yet.

[VBA]Option Explicit
Sub ReplaceBlanks()
Dim myRange As Range
Dim x As Range
Dim ws As Worksheet
Dim myrange2 As String



myrange2 = InputBox("EnterR", "EnterR")

For Each ws In Worksheets
ws.Select
Set myRange = ws.Range(myrange2)

For Each x In myRange

If x = "" And Not (IsEmpty(x.EntireRow)) Then x = "NoLongerBlank" 'Put your replacing string her


Next x

Next ws

End Sub[/VBA]

[Chisholm]

Sorry 'bout that!

[grichey]

Here it is finally. Mark as solved if this does it for you in thread tools at top of thread.
[VBA]Option Explicit
Sub ReplaceBlanks()
Dim myRange As Range
Dim x As Range
Dim ws As Worksheet
Dim myrange2 As String



myrange2 = InputBox("EnterR", "EnterR")

For Each ws In Worksheets
ws.Select
Set myRange = ws.Range(myrange2)

For Each x In myRange

If x = "" And Not (Application.CountA(x.EntireRow) = 0) Then x = "NoLongerBlank" 'Put your replacing string her


Next x

Next ws

End Sub[/VBA]

[Chisholm]

Thanks so much!!!

I only had to change one thing, but it's my fault for not providing better specifications (again...):
I changed the "And Not (Application.CountA(x.EntireRow) = 0)" to,
"And Not (Application.CountA(x.EntireRow) = 1)" , since there was "line item" column on each worksheet that was already filled out with "L1,L2,....,Li" where i=rows in sheet.
Making that change forced me to read and understand what the code was doing.
-Chisholm Chase