H
Hasan
Hi,
How do i write a macro to populate cell value from another sheet based
upon the data validation list selection
How do i write a macro to populate cell value from another sheet based
upon the data validation list selection
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Macro to populate cell value from another sheet based upon the datavalidation list selection
H
Hasan
You haven't explainedpopulatewhatcellwith whatdatafrom where,
however here's something you can work with, i havedatavalidationin G1
and my lookup table in M1:N12 and i want my result in A1
Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cVal
If Target.Address <> "$G$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
cVal = Application.WorksheetFunction.VLookup(Target, Range("M1:N12"), 2, 0)
Range("A1") = cVal
End Sub
--------------------
*How to Save a Worksheet EventMacro*
1. *Copy* themacroabove placing the cursor to the left of the
code box hold the *CTRL & Left Click,* then *Right Click* selected code
and *Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet themacrowill run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* themacrocode using *CTRL+V*
5. Make any custom changes to themacroif needed at this time.
6. *Save* themacroin your Workbook using *CTRL+S*
Hasan;556077 said:How do i write amacrotopopulatecellvaluefrom another sheetbased
uponthedatavalidationlistselection
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=153354
Microsoft Office Help
I have a data validation list in Sheet1.A1:A2000 where source of data
validation list is Sheet3.A1:A2000.
Depending upon the selection from the validation list in Sheet1 I want
the other columns(B,D,F,G) in Sheet1 to populate its corresponding
values in Sheet3.Column C,F,G,M
H
Hasan
Hasan, why have 2000 cells withdatavalidationin sheet 1 and have thelistin sheet 3, surely it would be better just to have thelistin
sheet 1 too?
Hasan;557248 Wrote:
Microsoft Office Discussion' (http://www.thecodecage.com)) Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
baseduponthe datavalidationlistselection- The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=153354)I have adatavalidationlistin Sheet1.A1:A2000 where source ofdata
validationlistis Sheet3.A1:A2000.Dependingupontheselectionfrom thevalidationlistin Sheet1 I want
the other columns(B,D,F,G) in Sheet1 topopulateits corresponding
values in Sheet3.Column C,F,G,M
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=153354
Microsoft Office Help- Hide quoted text -
- Show quoted text -
I am trying to create an automated procedure, where in the User
1. Exports the data into an excel file(Say Sheet3)
2. Selects the value in Sheet1.Column A (which is data validation list
from sheet3.Column A)
3. Depending upon the criteria in below code it updates the data in
relavent sheet else gives error.
4. I need to further automize the things, like Depending upon the
selection from the validation list in Sheet1 I want the other columns
(B,D,F,G) in Sheet1 to populate its corresponding
values in Sheet3.Column C,F,G,M
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim wsLoop As Worksheet
Dim FoundCell As Range
Dim myAddr As String
Dim TopRng As Range
Dim BotRng As Range
Dim BigRng As Range
Dim LastRow As Long
Dim FirstRow As Long
Dim res As Variant
myAddr = "A2:A2000"
With Sh.Range(myAddr)
FirstRow = .Row
LastRow = .Rows(.Rows.Count).Row
End With
If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If
If Target.Value = "" Then
'do nothing
Else
For Each wsLoop In ThisWorkbook.Worksheets
Select Case LCase(wsLoop.Name)
Case Is = LCase("Sheet3")
'skip it
Case Else
Set BigRng = wsLoop.Range(myAddr)
If LCase(wsLoop.Name) = LCase(Sh.Name) Then
With BigRng
If Target.Row = FirstRow Then
'in row 2, don't include it
Set BigRng = .Resize(.Rows.Count -
1).Offset(1, 0)
Else
If Target.Row = LastRow Then
'in row 200, don't include it
Set BigRng = .Resize(.Rows.Count - 1)
Else
Set TopRng = wsLoop.Range("A" &
FirstRow _
& ":A" & Target.Row -
1)
Set BotRng = wsLoop.Range("A" &
Target.Row + 1 _
& ":A" & LastRow)
Set BigRng = Union(TopRng, BotRng)
End If
End If
End With
End If
With BigRng
Set FoundCell = .Cells.Find(what:=Target.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'not found
Else
MsgBox "That entry already exists here:" & vbLf _
& FoundCell.Address(external:=True)
Application.EnableEvents = False
Target.ClearContents
Application.Goto FoundCell, Scroll:=True 'or
false??
Application.EnableEvents = True
Exit For
End If
End Select
Next wsLoop
res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:R"), 18, False)
If IsError(res) Then
'no message
Else
If LCase(Sh.Name) = LCase(res) Then
'do nothing
Else
MsgBox Target.Value & " should be on " & res
Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True
End If
End If
End If
End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
H
Hasan
Hasan, why have 2000 cells withdatavalidationin sheet 1 and have thelistin sheet 3, surely it would be better just to have thelistin
sheet 1 too?Hasan;557248 Wrote:Microsoft Office Help- Hide quoted text -- Show quoted text -
I am trying to create an automated procedure, where in the User
1. Exports thedatainto an excel file(Say Sheet3)
2. Selects thevaluein Sheet1.Column A (which isdatavalidationlist
from sheet3.Column A)
3. Dependinguponthe criteria in below code it updates thedatain
relavent sheet else gives error.
4. I need to further automize the things, like Dependingupontheselectionfrom thevalidationlistin Sheet1 I want the other columns
(B,D,F,G) in Sheet1 topopulateits corresponding
values in Sheet3.Column C,F,G,M
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim wsLoop As Worksheet
Dim FoundCell As Range
Dim myAddr As String
Dim TopRng As Range
Dim BotRng As Range
Dim BigRng As Range
Dim LastRow As Long
Dim FirstRow As Long
Dim res As Variant
myAddr = "A2:A2000"
With Sh.Range(myAddr)
FirstRow = .Row
LastRow = .Rows(.Rows.Count).Row
End With
If Intersect(Target, Sh.Range(myAddr)) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub 'singlecellat a time
End If
If Target.Value= "" Then
'do nothing
Else
For Each wsLoop In ThisWorkbook.Worksheets
Select Case LCase(wsLoop.Name)
Case Is = LCase("Sheet3")
'skip it
Case Else
Set BigRng = wsLoop.Range(myAddr)
If LCase(wsLoop.Name) = LCase(Sh.Name) Then
With BigRng
If Target.Row = FirstRow Then
'in row 2, don't include it
Set BigRng = .Resize(.Rows.Count -
1).Offset(1, 0)
Else
If Target.Row =LastRow Then
'in row 200, don't include it
Set BigRng = .Resize(.Rows.Count - 1)
Else
Set TopRng = wsLoop.Range("A" &
FirstRow _
& ":A" & Target.Row -
1)
Set BotRng = wsLoop.Range("A" &
Target.Row + 1 _
& ":A" & LastRow)
Set BigRng = Union(TopRng, BotRng)
End If
End If
End With
End If
With BigRng
Set FoundCell = .Cells.Find(what:=Target.Value, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
'not found
Else
MsgBox "That entry already exists here:" & vbLf _
& FoundCell.Address(external:=True)
Application.EnableEvents = False
Target.ClearContents
Application.Goto FoundCell, Scroll:=True 'or
false??
Application.EnableEvents = True
Exit For
End If
End Select
Next wsLoop
res _
= Application.VLookup(Target.Value, Worksheets("Sheet3").Range
("A:R"), 18, False)
If IsError(res) Then
'no message
Else
If LCase(Sh.Name) = LCase(res) Then
'do nothing
Else
MsgBox Target.Value& " should be on " & res
Application.EnableEvents = False
Target.Value= ""
Application.EnableEvents = True
End If
End If
End If
End Sub
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx- Hide quoted text -
- Show quoted text -
Can helps please.
D
David
THanks Simon...this helped me out Greatly!
Simon Lloyd said:You haven't explained populate what cell with what data from where,
however here's something you can work with, i have data validation in G1
and my lookup table in M1:N12 and i want my result in A1
Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cVal
If Target.Address <> "$G$1" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
cVal = Application.WorksheetFunction.VLookup(Target, Range("M1:N12"), 2, 0)
Range("A1") = cVal
End Sub
--------------------
*How to Save a Worksheet Event Macro*
1. *Copy* the macro above placing the cursor to the left of the
code box hold the *CTRL & Left Click,* then *Right Click* selected code
and *Copy.*
2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab*
for the Worksheet the macro will run on.
3. *Left Click* on *View Code* in the pop up menu.
4. *Paste* the macro code using *CTRL+V*
5. Make any custom changes to the macro if needed at this time.
6. *Save* the macro in your Workbook using *CTRL+S*
Hasan;556077 said:Hi,
How do i write a macro to populate cell value from another sheet based
upon the data validation list selection
--
Simon Lloyd
Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=153354
Microsoft Office Help
.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.