adding record on row

[PANKAJUTEKAR]

All experts,

Good afternoon all.

The following code i got from Sektor sir.

Now the problem is-
- record need to add from range A16 to range F16 and so on.
- value that are stored in range A16 to range F16 that is as follows,
f6 = a16, f7=b16, f5=c16, f9=d16, f10=e16, f11=f16.

now, every time i try to put this value on every new row i.e- a17 to f17
but following code is properly working.

Require kind solution from experts.

Sub addrecord()
Dim lastRow As Long, arr As Variant
lastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If lastRow = 2 Then lastRow = 4
'(ROWINDEX, COLINDEX)
arr = Array(Cells(6, 6), Cells(6, 7), Cells(6, 5), Cells(6, 8), Cells(6, 9), Cells(6, 10))
Cells(lastRow, 4).Resize(, 6) = arr
'Range("F5,F6").ClearContents
End Sub
===========================


I GOT IT EXPERTS......
sORRY FOR DISTRUBING YOU.
tHANK yOU ALL.

 

[Sektor]

Where are source ranges located?

 

[PANKAJUTEKAR]

Sir,
I got the solution...

My this problem is not getting solved from last 1 hour.

is there any facility to attach screen shot?
this is not a problem, so i can show u the excatly.

 

[PANKAJUTEKAR]

Dear Sir,

Kindly suggest for the following code.

wht i am doing, i used vlookup for getting the value from sheet3 to sheet4. (red marking u can see)

main problem i got on range - f8 on sheet4
f8 not pick up the value from sheet3.

if that wil done then my problrm get soled.

Require your kind comment on this Sir.

Private Sub ListBox1_Click()
If ListBox1.Value = "Motor" Then
Range("A10") = ListBox1.Value
Range("B10") = ""
ComboBox1.Enabled = True
ComboBox2.Enabled = False
Else
If ListBox1.Value = "SFU" Then
Range("B10") = ListBox1.Value
Range("A10") = ""
ComboBox1.Enabled = False
ComboBox2.Enabled = True
End If
End If
Range("F6") = ListBox1.Value
End Sub

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"
Range("A1:J30").Value = Range("A1:J30").Value
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
End Sub

Sub HideSheet()
Sheet3.Visible = xlSheetVeryHidden
Sheet3.Visible = True
End Sub

Private Sub CommandButton2_Click()
Call cleardata
End Sub

Sub cleardata()
If Range("A16").Select Then
Rows("16:16").Select
Selection.Delete Shift:=xlUp
End If