Solved

Looking for an easy simple way to select an entire group of columns and rows and convert the text to "1"

Posted on 2011-09-13
9
175 Views
Last Modified: 2012-05-12
Looking for an easy simple way to select an entire group of columns and rows and convert the text to "1"

Please look at the attached Excel file for an example.
Excel-Question.xlsx
0
Comment
Question by:Donnie Walker
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:SANTABABY
Comment Utility
Example VBA:



Dim r As Range

Set r = Rows(15)
'or
Set r = Columns(1)

r.Cells.Value = 1
0
 

Author Comment

by:Donnie Walker
Comment Utility
How would I do that in Excel and will that do multiple columns?
0
 
LVL 10

Expert Comment

by:SANTABABY
Comment Utility
Change any one cell value to 1
Copy the above cell (ctrl+c)
Ctrl+Click or ctrl+Mouse Select all the cells/ranges that you want to update
Paste (ctrl+v)
0
 

Author Comment

by:Donnie Walker
Comment Utility
That doesn't work the way I want. Some areas would have no data and this put in "1" everywhere.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 5

Accepted Solution

by:
slycoder earned 250 total points
Comment Utility
Try:

Put this macro in a module:

Public Sub ReplaceTextWithOne()
    For Each c In Selection
        If c.Value <> "" Then
            c.Value = "1"
        End If
    Next
End Sub


then select a range and run the macro.  Think this will do what you want.

0
 
LVL 3

Assisted Solution

by:heideld
heideld earned 250 total points
Comment Utility
Highlight desired columns and rows, press CTRL F (to find) choose the replace tab...

put a * in "Find What"
and a 1 in Replace with:

press replace all:
0
 
LVL 3

Expert Comment

by:heideld
Comment Utility
You said you wanted simple.....  and you can select multiple groups of columns and rows by holding the  the ctrl key
0
 
LVL 5

Expert Comment

by:slycoder
Comment Utility
Getting more complex - if you have cells that contain text, blanks and values and you wish to only replace the "TEXT" cells with a "1"

The ReplaceExclusiveTextWithOne is your function.


Public Sub ReplaceExclusiveTextWithOne()
    For Each c In Selection
   
        If CellType(c) = "Text" Then
            c.Value = "1"
        End If
    Next
End Sub

Function CellType(c)
    ' Source: http://j-walk.com/ss/excel/tips/tip62.htm
'   Returns the cell type of the upper left
'   cell in a range
    Application.Volatile
    Set c = c.Range("A1")
    Select Case True
        Case IsEmpty(c): CellType = "Blank"
        Case Application.IsText(c): CellType = "Text"
        Case Application.IsLogical(c): CellType = "Logical"
        Case Application.IsErr(c): CellType = "Error"
        Case IsDate(c): CellType = "Date"
        Case InStr(1, c.Text, ":") <> 0: CellType = "Time"
        Case IsNumeric(c): CellType = "Value"
    End Select
End Function



0
 

Author Closing Comment

by:Donnie Walker
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now