jquestion
asked on
excel macro
Hello, I am trying to create a macro that will change when the user only uses the drop down and it will populate the row with data from the vlookup. I have the following code but am stuck like braindead. Im learning and could use an experts help! Thanks!
Private Sub Worksheet_Change(ByVal NameBox As Range)
Dim c As Range
Range ("c4:m4")
'For Each c In Range("c4:m4")
'If Not IsNumeric(c) Then
c.ClearContents
' Next c
'End If
Insert_Block
End Sub
Option Explicit
Const SUMMARY_COLOR = 48
Const DETAIL_COLOR = 36
Sub Insert_Block()
With Range("C4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,3,FALSE)"
.Value = .Value
End With
With Range("D4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,4,FALSE)"
.Value = .Value
End With
With Range("E4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,5,FALSE)"
.Value = .Value
End With
With Range("F4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,6,FALSE)"
.Value = .Value
End With
With Range("G4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,7,FALSE)"
.Value = .Value
End With
With Range("H4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,8,FALSE)"
.Value = .Value
End With
With Range("I4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,9,FALSE)"
.Value = .Value
End With
With Range("J4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,10,FALSE)"
.Value = .Value
End With
With Range("K4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,11,FALSE)"
.Value = .Value
End With
With Range("L4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,12,FALSE)"
.Value = .Value
End With
With Range("M4")
.Formula = "=VLOOKUP( B4,'EXCEPTION % SUMMARY'!$A:$N,13,FALSE)"
.Value = .Value
End With
End Sub
could you pl attach yr excel sheet with macro for fast reply!
ASKER
ASKER
This is the file Im trying to get to work. any help would be greatly appreciated
TestMacro.xlsm
TestMacro.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome, Thanks!