Filter and replace content

[Rasha]

Hi,

I am trying to create a vba code to filter a column B based on some criteria and then I want to replace the contents of column A of the filtered rows by the value of column A and column B example :

if in column B i have value C0 for example, I want the content of column A to be equal to Chiah C0.

I have succeeded in writing a code to do the filter but I am unable to write the code to replace the content of only the filtered cell.

A file is uploaded as an example.

Thank you in advance.

Regards,

Rasha

 

[Marc L]

Hi !​

I want to replace the contents of column A of the filtered rows by the value of column A and column B example

Sure ? 'Cause it's not the same in your sample workbook ! …

Post your code with codetags …
​

 

[Rasha]

Hi !​

Sure ? 'Cause it's not the same in your sample workbook ! …

Post your code with codetags …
​

Sub Filter()
    Application.Workbooks("import_NEU.xlsm").Worksheets("import_neu").Activate
   
    Dim i As Integer
    Dim LR As Integer
   
    With ActiveSheet
        '  set column you filter for
           .Range("A:V").Select
        ' filters for emission 12/2011 and 02/2012
           Selection.AutoFilter Field:=4, Criteria1:="12/2011", Operator:=xlOr, _
           Criteria2:="=02/2012"
        'Filter chiah Disks
           .UsedRange.AutoFilter Field:=3, Criteria1:=Array("C0", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "CA", "CB", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "DA", "DB", "DC", "DD", "DE", "DF", "F1", "F2", "F3", "F4", "F5", "F6", "F7"), Operator:=xlFilterValues
        'Delete filtered cells
           .UsedRange.Offset(1, 0).SpecialCells _
           (xlCellTypeVisible).EntireRow.Delete
        'remove filter on emissions
           .UsedRange.AutoFilter Field:=4
           
        'LR = .UsedRange.Rows.Count
       
        ' For every row of the filtered cells I want to change column B to be equal to "Chiah "& value of column C
        ' "Filtered cell"
        End With
End Sub

 

[Marc L]

'Cause of your very bad sample workbook
(only 3 columns but Field 4 in code ‼),
I just can try on active filter :
​

Sub Filter()
    Application.ScreenUpdating = False
''    Application.Workbooks("import_NEU.xlsm").Worksheets("import_neu").Activate

    '  set column you filter for
    With ActiveSheet.Cells(1).CurrentRegion
        ' filters for emission 12/2011 and 02/2012
''          .AutoFilter Field:=4, Criteria1:="12/2011", Operator:=xlOr, Criteria2:="=02/2012"
        'Filter chiah Disks
''          .UsedRange.AutoFilter Field:=3, Criteria1:=Array("C0", "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "CA", "CB", "D1", "D2", "D3", "D4", "D5", "D6", "D7", "D8", "D9", "DA", "DB", "DC", "DD", "DE", "DF", "F1", "F2", "F3", "F4", "F5", "F6", "F7"), Operator:=xlFilterValues
        'Delete filtered cells
''        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        'remove filter on emissions
''        .AutoFilter 4

        ' For every row of the filtered cells amend column B to be equal to "Chiah "& value of column C
         With .Columns(2).Offset(1, 1).Resize(.Rows.Count - 1)
             .Formula = "=""Chiah "" & B2"
             .Formula = .Value
        End With
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Rasha]

Hi, I think you misunderstood me.I have uploaded the sample workbook to show you the output I want only. It doesn't reflect the code. The code is working properly and I have no problem with it (It does the filter) BUT

ALL I want is to loop through the filtered rows and replace the content of a cell of column C with ("Chiah "& column B) value. I want my code to run on the filtered rows only. Note that the filter is on column B
This .Formula = "=""Chiah "" & B2" isn't what I need because each row might have a different column B value.

So basically what will be the code to loop through the filtered rows?

I hope I made it clear.

Thank you for your time and help! I appreciate it much.

Best regards,

Rasha

 

[Marc L]

Oh, you wrote that without any run of my code ‼ ​

 

[Rasha]

Hi Marc, Sure I tried your code, it is successfully looping through the filtered rows. However, it replaces the content with "Chiah "&B2 and this is the problem. For every row I have a different value in B column. So rather than having B2, I wanted B(i) where i is the row number of the filtered cells only.

Please help!

Thank you in advance

Regards,

Rasha

 

[shrivallabha]

You'd have to add a test to see if a cell belongs to visible cells category or not.
e.g.

Sub FilterAndUpdate()
Dim r As Range, rSource As Range
Dim lngLastRow As Long
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rSource = Range("A1:V" & lngLastRow)
rSource.AutoFilter Field:=2, Criteria1:="Change here!"
For i = 2 To lngLastRow
  If Not Intersect(Range("A" & i), Range("A2:V" & lngLastRow).SpecialCells(xlCellTypeVisible)) Is Nothing Then
  Range("A" & i).Value = Range("A" & i).Value & " " & Range("B" & i).Value
  End If
Next
rSource.AutoFilter
End Sub

Edit: The code is rough wrt variables.

 

[Rasha]

Hi Shrivallabha,

Code runs perfectly with slight modifications to match filter criteria

Big THANK YOU

Regards,

Rasha