[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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

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
Donnie Walker
Asked:
Donnie Walker
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
SANTABABYCommented:
Example VBA:



Dim r As Range

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

r.Cells.Value = 1
0
 
Donnie WalkerAuthor Commented:
How would I do that in Excel and will that do multiple columns?
0
 
SANTABABYCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Donnie WalkerAuthor Commented:
That doesn't work the way I want. Some areas would have no data and this put in "1" everywhere.
0
 
slycoderCommented:
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
 
heideldCommented:
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
 
heideldCommented:
You said you wanted simple.....  and you can select multiple groups of columns and rows by holding the  the ctrl key
0
 
slycoderCommented:
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
 
Donnie WalkerAuthor Commented:
Thanks
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now