none
Macro - Replacing Contents of Cells RRS feed

  • Question

  •  

     

    When dealing with the a spreadsheet, is there a macro code that would replace the contents of a cell with a "0" for all of the cells in each column in the Questions worksheet that do not match the criteria from a specific cell in the Solutions worksheet?

     

    I received wonderful help for a similar question on the same worksheet with the following original question:

     

    I am trying to write a macro that will use one sheet, Solutions (Sheet 1), with a specific cell range, B2, and find and replace the reference from cell B2 in a specific column, G, in Sheet 2, Questions, and replace with the value 1.  Right now I am using B2 from Solutions Sheet 1 that contains the cell named Financial (Revenue) reporting, and using that as a specific search criteria in column G, but is there a way to use the reference B2 and find and replace cells in column G that are also labeled Financial (Revenue) reporting without using the actual name "Financial (Revenue) reporting" but if the cell in column G, Sheet 2 Questions, matches B2, Sheet 1 Solutions, then replace that specific value in column G, Sheet 2 Questions, with a value of "1"?

     

    The following is the outcome from that original question that I am building my spreadsheet from:

     

    Code Snippet

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 5/8/2007 by Rachel
    '

    '
        Sheets("Questions").Range("G:G").Replace What:=Sheets("Solutions").Range("B2"), Replacement:="1", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
            False, ReplaceFormat:=False

    End Sub

     

    Thank you!

    Wednesday, May 9, 2007 9:25 PM

All replies

  • Here is a simple sub that does that for you

     

    Code Snippet

    Sub Replace_For_Rachel()

    Dim rGCol As Range
    Dim sB2Text As String

    Set rGCol = Range("G2:G" & CStr(Range("G:G").Cells.SpecialCells(xlCellTypeLastCell).Row))
    sB2Text = Range("B2").Value

    For Each gcell In rGCol
        If gcell.Value = sB2Text Then
            gcell.Value = 1
        Else
            gcell.Value = 0
        End If
    Next gcell

    End Sub

     


     

     

    Cheers

    Shasur

    Friday, May 11, 2007 12:09 PM