Solved: Edit code to change cell value

[marshybid]

Morning All,

xld recently helped me out with a filtering problem, essentially the code he helped with identifies specific cell reference criteria then, if the criteria is met the entire row is deleted (code below);

Private Sub DeleteUnwanted(ByVal sh As String, _
                           ByVal TestColumn As Long, _
                           ByVal Criteria As String)
'// Filter the nominated column on nominated criteria and delete all visible rows
Dim cRows As Long
    With Worksheets(sh)
    
        On Error Resume Next
        'first, count the rows to operate on
        cRows = .Cells(.Rows.Count, TestColumn).End(xlUp).Row
             
        'finally, apply the autofilter for al matching cells
        .Columns(TestColumn).AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlAnd
             
        'we now have only matching rows visible, so we can
        'delete these matching rows
        With .Cells(2, TestColumn).Resize(cRows - 1)
                
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
             
        .Columns(TestColumn).AutoFilter
        On Error GoTo 0
    End With
End Sub

What I would like to do now, is use the filtering method to identify if a cell contains "N", if it does then the cell 2 to the left (RC[-2]) will have to be changed to equal the value of the cell one to the left (RC[-1])

I am struggling with how to edit the code to do this.

Any help greatly appreciated.

Marshybid

[Bob Phillips]

[vba]

Public Sub TestIt()
ChangeValues "VLookup", 3, "N", -2, -1
End Sub

Private Sub ChangeValues(ByVal sh As String, _
ByVal TestColumn As Long, _
ByVal Criteria As String, _
ByVal ColOffset1 As Long, _
ByVal ColOffset2 As Long)
'// Filter the nominated column on nominated criteria and delete all visible rows
Dim cRows As Long
Dim cell As Range

With Worksheets(sh)

On Error Resume Next
'first, count the rows to operate on
cRows = .Cells(.Rows.Count, TestColumn).End(xlUp).Row

'finally, apply the autofilter for al matching cells
.Columns(TestColumn).AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
For Each cell In .Cells(2, TestColumn).Resize(cRows - 1).SpecialCells(xlCellTypeVisible)

cell.Offset(0, ColOffset1).Value = cell.Offset(0, ColOffset2).Value
Next cell

.Columns(TestColumn).AutoFilter
On Error GoTo 0
End With
End Sub
[/vba]

[marshybid]

[vba]

Public Sub TestIt()
ChangeValues "VLookup", 3, "N", -2, -1
End Sub

Private Sub ChangeValues(ByVal sh As String, _
ByVal TestColumn As Long, _
ByVal Criteria As String, _
ByVal ColOffset1 As Long, _
ByVal ColOffset2 As Long)
'// Filter the nominated column on nominated criteria and delete all visible rows
Dim cRows As Long
Dim cell As Range

With Worksheets(sh)

On Error Resume Next
'first, count the rows to operate on
cRows = .Cells(.Rows.Count, TestColumn).End(xlUp).Row

'finally, apply the autofilter for al matching cells
.Columns(TestColumn).AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
For Each cell In .Cells(2, TestColumn).Resize(cRows - 1).SpecialCells(xlCellTypeVisible)

cell.Offset(0, ColOffset1).Value = cell.Offset(0, ColOffset2).Value
Next cell

.Columns(TestColumn).AutoFilter
On Error GoTo 0
End With
End Sub
[/vba]

Thanks xld, so would I just paste this code at the end of my macro (the one you helped me with last week) and then Call TestIt??? or Call ChangeValues??

My guess is Call ChangeValues

Marshybid

[marshybid]

[vba]

Public Sub TestIt()
ChangeValues "VLookup", 3, "N", -2, -1
End Sub

Private Sub ChangeValues(ByVal sh As String, _
ByVal TestColumn As Long, _
ByVal Criteria As String, _
ByVal ColOffset1 As Long, _
ByVal ColOffset2 As Long)
'// Filter the nominated column on nominated criteria and delete all visible rows
Dim cRows As Long
Dim cell As Range

With Worksheets(sh)

On Error Resume Next
'first, count the rows to operate on
cRows = .Cells(.Rows.Count, TestColumn).End(xlUp).Row

'finally, apply the autofilter for al matching cells
.Columns(TestColumn).AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
For Each cell In .Cells(2, TestColumn).Resize(cRows - 1).SpecialCells(xlCellTypeVisible)

cell.Offset(0, ColOffset1).Value = cell.Offset(0, ColOffset2).Value
Next cell

.Columns(TestColumn).AutoFilter
On Error GoTo 0
End With
End Sub
[/vba]

Thanks xld, so would I just paste this code at the end of my macro (the one you helped me with last week) and then Call TestIt??? or Call ChangeValues??

My guess is Call ChangeValues

Marshybid

[marshybid]

[vba]

Public Sub TestIt()
ChangeValues "VLookup", 3, "N", -2, -1
End Sub

Private Sub ChangeValues(ByVal sh As String, _
ByVal TestColumn As Long, _
ByVal Criteria As String, _
ByVal ColOffset1 As Long, _
ByVal ColOffset2 As Long)
'// Filter the nominated column on nominated criteria and delete all visible rows
Dim cRows As Long
Dim cell As Range

With Worksheets(sh)

On Error Resume Next
'first, count the rows to operate on
cRows = .Cells(.Rows.Count, TestColumn).End(xlUp).Row

'finally, apply the autofilter for al matching cells
.Columns(TestColumn).AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
For Each cell In .Cells(2, TestColumn).Resize(cRows - 1).SpecialCells(xlCellTypeVisible)

cell.Offset(0, ColOffset1).Value = cell.Offset(0, ColOffset2).Value
Next cell

.Columns(TestColumn).AutoFilter
On Error GoTo 0
End With
End Sub
[/vba]

Thanks xld, so would I just paste this code at the end of my macro (the one you helped me with last week) and then Call TestIt??? or Call ChangeValues??

My guess is Call ChangeValues

Marshybid

[Bob Phillips]

So good, so good, so good you said it thrice!

The code I gave is generic, the procedure handles different sheets, criteria, column offsets, so you could remove all of that flexibility and hone in on your needs.

The code that I give in the TestIt procedure would be what you add to your code at the point that you wish to make that data change.

[marshybid]

So good, so good, so good you said it thrice!

The code I gave is generic, the procedure handles different sheets, criteria, column offsets, so you could remove all of that flexibility and hone in on your needs.

The code that I give in the TestIt procedure would be what you add to your code at the point that you wish to make that data change.

Done, done, done..... Don't know how that happened!!!

Thanks xld, I have been able to successfully incorporate this into the main macro now and I now have all the correct data in the final output.

Once again thanks for all of your help. Marking thread as solved

Marshybid