kreso
05-22-2016, 11:28 PM
I have excel table that I am using (you can see it in attachment), and i made the two codes that you can see lower.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'// Runs when a value in B5 to B100 is changed
If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
Range("E5:E100").Value = Range("C5:C100").Value
Range("F5:F100").Value = Range("D5:100").Value
End If
End Sub
The thing is that when i manually add some different value (not the one that is auto generate with VLOOKUP function) in some E cell (for example E8), when i pick another choice from drop down list in B column (B9), vba code automatically change my manually entered value to value that is pulled from VLOOKUP.
Is there a way to make vba copy/paste values only for row that is in that moment "active", because now it every time when i make change it copy/paste whole range?
Thanks in advance
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'// Runs when a value in B5 to B100 is changed
If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
Range("E5:E100").Value = Range("C5:C100").Value
Range("F5:F100").Value = Range("D5:100").Value
End If
End Sub
The thing is that when i manually add some different value (not the one that is auto generate with VLOOKUP function) in some E cell (for example E8), when i pick another choice from drop down list in B column (B9), vba code automatically change my manually entered value to value that is pulled from VLOOKUP.
Is there a way to make vba copy/paste values only for row that is in that moment "active", because now it every time when i make change it copy/paste whole range?
Thanks in advance