vlookup overwrite current value

[SAM MADZLAN]

Hi All,

I have 1 filtered data that already been processed(1st set). with my 2nd set code, it overwrites current(1st set) value. Is there any way to make my code only process visible cell.

1st set

Sub LookMAL1()
Dim x As String
Dim LastRow As Long


LastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("M2").FormulaR1C1 = _
"=VLOOKUP(RC[-1],'MAL1'!C2:C8,7,0)"
Range("M2").Copy Range("M3:M" & LastRow)
        
Range("N2").FormulaR1C1 = _
"=VLOOKUP(RC[-2],'MAL1'!C2:C9,8,0)"
Range("N2").Copy Range("N3:N" & LastRow)
End Sub

2nd set

Sub LookMAL6()


Dim LastRow As Long


LastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("M2").FormulaR1C1 = _
"=VLOOKUP(RC[-1],'MAL6'!C2:C8,7,0)"
Range("M2").Copy Range("M3:M" & LastRow)
        
Range("N2").FormulaR1C1 = _
"=VLOOKUP(RC[-2],'MAL6'!C2:C9,8,0)"
Range("N2").Copy Range("N3:N" & LastRow)


End Sub

 

[James006]

Hello,

Sorry but cannot see the difference between what you are calling ' 1st set ' and ' 2nd set ' ...?

 

[SAM MADZLAN]

1st set is for MAL1 and the 2nd set is MAL6. I have to lookup the MAL1 then MAL6.

 

[James006]

MAL1 and MAL6 could be the same variable ...

 

[Fluff]

If it's the activesheet that has been filtered, try

Sub LookMAL6()


Dim LastRow As Long
Dim Cl As Range

LastRow = Range("D" & Rows.Count).End(xlUp).Row
For Each Cl In Range("M2:M" & LastRow).SpecialCells(xlVisible)
   Cl.FormulaR1C1 = "=VLOOKUP(RC[-1],'MAL6'!C2:C8,7,0)"
   Cl.Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC[-2],'MAL6'!C2:C9,8,0)"
Next Cl

End Sub