?
Solved

Search and Replace Question

Posted on 2011-05-02
7
Medium Priority
?
201 Views
Last Modified: 2012-05-11
Hi,
I need to create a search and replace formula, to be executed via a Macro. Is this possible?

The formula goes like this:

Find every empty cell, from row 2 column C, for a populated record in a sheet and replace it with the value of cell row 1 column X, and include a '<' symbol as a prefix.

Example Before
Row      Description      1.0      2.0      2.1      1.3
Record1      Blah                  2.0      
Record2      Blah      0.5                  1.2
Record3      Blah                        

Example After
Row      Description      1.0      2.0      2.1      1.3
Record1      Blah      <1.0      <2.0      2.0      <1.3
Record2      Blah      0.5      <2.0      <2.1      1.2
Record3      Blah      <1.0      <2.0      <2.2      <1.3
0
Comment
Question by:mullykid
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35507575
Assuming that this is for the used range (you state populated record) you can use the following:
 
Sub fillEmpty()
Application.ScreenUpdating = False
    For Each x In ActiveSheet.UsedRange
        If x.Value = "" Then x.Value = "<" & Cells(1, x.Column).Value
    Next
Application.ScreenUpdating = True
End Sub

Open in new window

This assumes that they are actually empty cells i.e. do not contain non-visible space characters.

If you prefer not to use it for the used range, but instead to pre-select the required range you could amend this to:
 
Sub fillEmpty()
Application.ScreenUpdating = False
    For Each x In Selection
        If x.Value = "" Then x.Value = "<" & Cells(1, x.Column).Value
    Next
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 35507602
This code will check how many columns and rows you have and will do what you want. I've attached a working sample for you to test.

jppinto
Sub FindReplace()
Dim lstRow As Long
Dim lstCol As Long
Dim x As Long
Dim y As Long

lstRow = Sheets("Sheet1").Cells(Sheets("Sheet1").rows.Count, "A").End(xlUp).row
lstCol = Sheets("Sheet1").Cells(1, Sheets("Sheet1").columns.Count).End(xlToLeft).Column


For x = 2 To lstRow
    For y = 3 To lstCol
        If Sheets("Sheet1").Cells(x, y).Value = "" Then
            Sheets("Sheet1").Cells(x, y).Value = "<" & Sheets("Sheet1").Cells(1, y).Value
        End If
    Next y
Next x

End Sub

Open in new window

FindandReplace-VBA.xlsm
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35507615
Maybe this?
Sub x()

Dim r As Long

r = Range("A" & Rows.Count).End(xlUp).Row

With Range("C2:F" & r)
    .SpecialCells(xlCellTypeBlanks).Formula = "=""<"" & C$1"
    .Value = .Value
End With

End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mullykid
ID: 35507684
Thanks guys. I'll check those methods out.

The amount of records and columns will be variable, so the code can't have limits set.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35507693
My code checks for the number of rows and columns!
0
 
LVL 7

Expert Comment

by:philip m o'brien
ID: 35507731
None of the methods given limit your selection size, just remember to turn screen updating off/on again if you are dealing with larger ranges (or, ideally, for any size). This will ensure your chosen routine runs quickly even for large data sets.
And, on a final note regards my point about space filled cells, you could adjust with the below to handle those as well:
 
Sub fillEmpty()
Application.ScreenUpdating = False
    For Each x In ActiveSheet.UsedRange
    On Error Resume Next
        x.Value = Replace(x.Value, " ", "")
        If x.Value = "" Then x.Value = "<" & Cells(1, x.Column).Value
    Next
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 

Author Comment

by:mullykid
ID: 35507801
Thanks guys!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question