sg2209
01-31-2018, 04:35 AM
Dear Friends ,
i am new in VBA and trying to learning an grabs new things by going through the Forums Discussions & by watching videos , just prepare a code , that look up from the closed work book unfortunately it is taking around 10 minutes to get the values also , there are duplicate entries in Look up array table ,so it only picks up the blank cell because vVlook from gets the value of First Cell is there any way if Vlook array table has any value then need to get it instead of blank .
below is the Code
[With Sheets("Refund Payable report")
[ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row]
With .Range("V2:V" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-21],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,7,FALSE),""NA"")"
.Value = .Value
End With
End With
With Sheets("Refund Payable report")
ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("W2:W" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-22],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,11,FALSE),""NA"")"
.Value = .Value
End With
End With
Please Review .
i am new in VBA and trying to learning an grabs new things by going through the Forums Discussions & by watching videos , just prepare a code , that look up from the closed work book unfortunately it is taking around 10 minutes to get the values also , there are duplicate entries in Look up array table ,so it only picks up the blank cell because vVlook from gets the value of First Cell is there any way if Vlook array table has any value then need to get it instead of blank .
below is the Code
[With Sheets("Refund Payable report")
[ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row]
With .Range("V2:V" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-21],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,7,FALSE),""NA"")"
.Value = .Value
End With
End With
With Sheets("Refund Payable report")
ilastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("W2:W" & ilastrow)
.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-22],'E:\Brm\[All Open Credits.xlsb]Sheet1'!R1C3:R468196C9,11,FALSE),""NA"")"
.Value = .Value
End With
End With
Please Review .