[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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