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
178 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
ID: 36531309
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
ID: 36531328
How would I do that in Excel and will that do multiple columns?
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 36531348
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
ID: 36531367
That doesn't work the way I want. Some areas would have no data and this put in "1" everywhere.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Accepted Solution

by:
slycoder earned 250 total points
ID: 36531423
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
ID: 36531429
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
ID: 36531445
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
ID: 36531464
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
ID: 36531471
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 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

12 Experts available now in Live!

Get 1:1 Help Now