how to get formula pasted in required cell in excel vba

Question

In Excel VBA i have a target column i.e Column M in which i am pasting my formula values based on my source column i.e Column C ( which is my filtering criteria) but i am not getting my formula in my required column instead formula gets pasted in all header as well

Please find the attached excel sheet

Please also find the code which i used to get the data More concern about case "Contain" Based on sheet "Mapping Rules" sheet i am using my formulas to manipulate data in "PMP Report" sheet

What i have observed is when there is only 1 filterd record in PMP_Report sheet it does not paste records in required column properly but when it has 2 or more filtered records then it shows proper output at required cell. Is there anyone who can help me to figure out how to get this done Still new to VBA programming but trying some automation for my work

Also if you want any doubts about this or need any additional information pls ask me

Case "contain"
    sh2.Activate
    sh2.Cells(1, l).Select
    Selection.AutoFilter
    sh2.Range("A1:N" & pmplastrow).AutoFilter Field:=sh2.Cells(2, l).Column, Criteria1:="=" & "*" & sh8.Cells(i, 4) & "*", Operator:=xlAnd
    For m = 1 To pmplastcol
        If InStr(sh8.Cells(i, 5), sh2.Cells(1, m)) <> 0 Then
            sh2.Cells(2, m).Select
            Set Rng = sh2.Range(Cells(2, m).Address & ":" & Cells(pmplastrow, m).Address).SpecialCells(xlCellTypeVisible) ' target range
            Set rng1 = sh2.Range(Cells(2, l).Address & ":" & Cells(pmplastrow, l).Address).SpecialCells(xlCellTypeVisible) ' source range
            Select Case sh8.Cells(i, 6)
                Case "between"
                    For k = 2 To pmplastrow
                        If Intersect(sh2.Cells(k, l), rng1) Is Nothing Then
                        Else
                            Rng.SpecialCells(xlCellTypeVisible).FormulaR1C1 = VBA.Trim(VBA.Mid(sh2.Cells(k, l), Excel.WorksheetFunction.Search(sh8.Cells(i, 7), sh2.Cells(k, l)) + VBA.Len(sh8.Cells(i, 7)), Excel.WorksheetFunction.Search(sh8.Cells(i, 8), sh2.Cells(k, l)) - Excel.WorksheetFunction.Search(sh8.Cells(i, 7), sh2.Cells(k, l)) - VBA.Len(sh8.Cells(i, 7))))
                        End If
                    Next k
                Case "is"
                    For k = 2 To pmplastrow
                        If Intersect(sh2.Cells(k, l), rng1) Is Nothing Then
                        Else
                            Rng.SpecialCells(xlCellTypeVisible).FormulaR1C1 = VBA.Trim(sh8.Cells(i, 7))
                        End If
                    Next k
            End Select
            sh2.ShowAllData
        End If
    Next m

Your code is a sitting time bomb. :) You need to avoid using `.Select/.Activate`. Work with objects. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). The next thing you need to work on is fully qualifying your Cells. You may want to see [Why does Range work, but not Cells?](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) — Siddharth Rout, Dec 19 '19 at 09:46
Yes i agree the code is not properly optimized and also i can avoid some select & activate statement also . well there is problem in my target range ( column M ) which is now empty as you can see in figure also which is rng. when there is only one filtered record ( in column B ) it is not properly taking that range in consideration — Milind Suryawanshi, Dec 19 '19 at 10:00

how to get formula pasted in required cell in excel vba

0 Answers0