Donnie Walker
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
Please look at the attached Excel file for an example.
Excel-Question.xlsx
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)
Copy the above cell (ctrl+c)
Ctrl+Click or ctrl+Mouse Select all the cells/ranges that you want to update
Paste (ctrl+v)
ASKER
That doesn't work the way I want. Some areas would have no data and this put in "1" everywhere.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ReplaceExclusiveTextWithOn e is your function.
Public Sub ReplaceExclusiveTextWithOn e()
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
The ReplaceExclusiveTextWithOn
Public Sub ReplaceExclusiveTextWithOn
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
ASKER
Thanks
Dim r As Range
Set r = Rows(15)
'or
Set r = Columns(1)
r.Cells.Value = 1