Link to home
Start Free TrialLog in
Avatar of Donnie Walker
Donnie WalkerFlag for United States of America

asked on

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
Avatar of SANTABABY
SANTABABY
Flag of United States of America image

Example VBA:



Dim r As Range

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

r.Cells.Value = 1
Avatar of Donnie Walker

ASKER

How would I do that in Excel and will that do multiple columns?
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)
That doesn't work the way I want. Some areas would have no data and this put in "1" everywhere.
ASKER CERTIFIED SOLUTION
Avatar of Carlos Ramirez
Carlos Ramirez
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of heideld
heideld

You said you wanted simple.....  and you can select multiple groups of columns and rows by holding the  the ctrl key
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



Thanks