Value retriving from ane sheet to another by using vlookup..

[PANKAJUTEKAR]

Dear Experts,

Thank You for reading this query.

I need help for following query. (red marked)

I try to take the value from sheet3 to sheet4 for rangef8.
I put vlookup for f8 but this is not taking the value from sheet3.

Private Sub ComboBox1_Click()
Range("F7") = ComboBox1.Value
'GETTING THE VALUE FOR COST
Range("F8").Select
ActiveCell.FormulaR1C1 = "=IF(R[-2]C=""Motor"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],2,FALSE)) "
'IF(R[-2]C=""SFU"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],2,FALSE)))"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=IF(R[-2]C=""Motor"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],3,FALSE))"
'IF(R[-2]C=""SFU"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],3,FALSE)))"
End Sub

Private Sub ComboBox2_Click()
Range("F7") = ComboBox2.Value
'GETTING THE VALUE FOR COST
Range("F8").Select
ActiveCell.FormulaR1C1 = "=IF(R[-2]C=""SFU"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],2,FALSE))"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=IF(R[-2]C=""SFU"",VLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],3,FALSE))"
End Sub

Private Sub CommandButton1_Click()
Range("F10").Select
If Range("F7") = "0.18 kW DOL" Then
ActiveCell.FormulaR1C1 = "=Sheet3!R[-7]C[-4]+Sheet4!R[-5]C"
End If
If Range("F7") = "0.25 kW DOL" Then
ActiveCell.FormulaR1C1 = "=Sheet3!R[-6]C[-4]+Sheet4!R[-5]C"
End If
'-------
Range("F11").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-6]C"
'-----------
End Sub

Private Sub CommandButton3_Click()
Call addrecord
ThisWorkbook.Activate
End Sub

Sub addrecord()
Dim lastRow As Long, arr As Variant
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If lastRow = 1 Then lastRow = 1
'(ROWINDEX, COLINDEX)
arr = Array(Cells(6, 6), Cells(7, 6), Cells(5, 6), Cells(9, 6), Cells(10, 6), Cells(11, 6))
Cells(lastRow, 1).Resize(, 6) = arr
Range("F5,F6,F7,F8,F9,F10,F11").ClearContents
Rows("16:16").Select
Selection.Insert Shift:=xlDown
End Sub

 

[gaurav23]

Hi,

You are trying to lookup in rows and not in columns.

i.e. in Vlookup we used C[-1] but for your query you have to use Hlookup.

"=IF(R[-2]C=""SFU"",HLOOKUP(R[-1]C,Sheet3!R[-5]C[-6]:R[16]C[-4],2,FALSE))"

Try this will work.