[RESOLVED] Change cell value via another cell value

[Beall49]

I have this code that I use to delete rows based off of a variable in the columns. It loops through and finds every row with that value and deletes the row.

I'm trying to do everything the same except instead of deleting a row, I want to change the value of a different cell.

Sub DeleteRows()

Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim mCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria
On Error Resume Next

   'Determine the table range
With Sheets("HRC").Cells.Select
   
         If .Cells.Count > 1 Then
             Set rTable = Selection
         Else
         Set rTable = .CurrentRegion
             On Error GoTo 0
         End If

    End With


    vCriteria = Application.InputBox(Prompt:="Type in the criteria that macthing rows should be deleted. " _
    & "If the criteria is in a cell, point to the cell with your mouse pointer", _
    Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)

    If vCriteria = "False" Then Exit Sub

lCol = 3

    

    'Set rCol to the column where criteria should be found
    Set rCol = rTable.Columns(lCol)

    'Set rCell to the first data cell in rCol
    Set rCell = rCol.Cells(2, 1)
    
   'Store current Calculation then switch to manual.
   xlCalc = Application.Calculation
   Application.Calculation = xlCalculationManual

   'Loop and delete as many times as vCriteria exists in rCol
   For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)

        Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(-1, 0)

          [B]rCell.Offset(1, 0).entirerow.delete[/B] 
             'THIS IS WHERE IT DELETES, NEED TO SELECT ANOTHER CELL AND 'CHANGE THE VALUE OF THAT CELL
          
   Next lCol

   

   'Put back calculation to how it was.
   Application.Calculation = xlCalc
   On Error GoTo 0

End Sub

[Siddharth Rout]

rCell.Offset(0, 1) = "your value"

will change the value of next cell in the same row...

rCell.Offset(0, 5) = "your value"

will change the value of the 5th cell on the right in the same row...

Also what if Set rCell = rCol.Find(What.... fails?

See this thread on how to use .find

http://www.vbforums.com/showpost.php...68&postcount=2

Hope this helps...

[Beall49]

That works but for some reason (I had gotten to this point before) it ends the For Next Loop.

There will normally be multiple values on a sheet, so it has to loop through the column looking for a specific value then change the cell to the right if it matches the value.

[Siddharth Rout]

I have suggested a better way of doing it... see the previous post...

[Beall49]

yeah I still can't get it to loop right...IDK what I'm doing wrong?

[Siddharth Rout]

ok let me see what can i do for you... give me few moments...

edit: Ok see this example on how to use .find and continue down the column without using for next loop...

http://www.vbforums.com/showpost.php...31&postcount=8