Want to do excel filtering activity in existing excel

Help Activities
,
1

IMG_20240226_215717
IMG_20240226_2157171920×2560 333 KB

In this excel want to apply filter in Amount column and get only empty cells and also check if the Value column contain any value then paste that B1 value -23.4 in empty cells otherwise if the value column doesn’t have eny value means leave it empty. We should not use read range activity for this. We should do it in existing excel.

2

Hi @Gopi_Krishna1

Try this code:

Sub ApplyFilterAndPasteValue()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each cell in the Amount column
    For i = 2 To lastRow ' Assuming data starts from row 2
        If ws.Cells(i, 1).Value = "" Then ' Check if the cell is empty
            If ws.Cells(i, 2).Value <> "" Then ' Check if corresponding cell in Value column is not empty
                ws.Cells(i, 1).Value = ws.Cells(1, 2).Value - 23.4 ' Paste the value from B1 - 23.4
            End If
        End If
    Next i
    
    ' Apply filter on Amount column to display only empty cells
    ws.Range("A1").AutoFilter Field:=1, Criteria1:="="
End Sub

Hope it helps

3

Instead of mention -23.5 mention as B1 value because values may change, provide code based on that

4

Try this @Gopi_Krishna1

Sub ApplyFilterAndPasteValue()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim valueToSubtract As Double
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Get the value from cell B1
    valueToSubtract = ws.Range("B1").Value
    
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each cell in the Amount column
    For i = 2 To lastRow ' Assuming data starts from row 2
        If ws.Cells(i, 1).Value = "" Then ' Check if the cell is empty
            If ws.Cells(i, 2).Value <> "" Then ' Check if corresponding cell in Value column is not empty
                ws.Cells(i, 1).Value = valueToSubtract ' Paste the value from B1
            End If
        End If
    Next i
    
    ' Apply filter on Amount column to display only empty cells
    ws.Range("A1").AutoFilter Field:=1, Criteria1:="="
End Sub

Hope it helps

5

It’s not working, will tell the scene in P Column we have to filter only empty cell and also look for A column parallel to it contains value if it contains values means we have to copy and paste P1 value otherwise leave it blank.

6

Getting error in excel in Getting value from B1 cell code line