Hi,

How do i write a macro to populate cell value from another sheet based
upon the data validation list selection

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 Wrote:
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/sh...d.php?t=153354

Microsoft Office Help

On Nov 12, 2:20*pm, Simon Lloyd
wrote:
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 Wrote:

Hi,

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/sh...d.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

Hasan, why have 2000 cells with data validation in sheet 1 and have the
list in sheet 3, surely it would be better just to have the list in
sheet 1 too?

Hasan;557248 Wrote:
On Nov 12, 2:20*pm, Simon Lloyd
wrote:
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 Wrote:

Hi,

How do i write amacrotopopulatecellvaluefrom another sheetbased
uponthedatavalidationlistselection

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Macro to populate cell value from another sheet
based upon the datavalidation list selection - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=153354)

'Microsoft Office Help' (http://&quot;http://www.thecodecage.com)

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


--
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/sh...d.php?t=153354

Microsoft Office Help

On Nov 13, 12:10*pm, Simon Lloyd
wrote:
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:





On Nov 12, 2:20*pm, Simon Lloyd
wrote:
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 Wrote:

Hi,

How do i write amacrotopopulatecellvaluefrom another sheetbased
uponthedatavalidationlistselection

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Macrotopopulatecellvaluefrom another sheet
baseduponthe datavalidationlistselection- The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=153354)

'Microsoft Office Help' (http://&quot;http://www.thecodecage.com)

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/sh...d.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 he" & 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

On Nov 16, 11:40*pm, Hasan wrote:
On Nov 13, 12:10*pm, Simon Lloyd
wrote:





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:

On Nov 12, 2:20*pm, Simon Lloyd
wrote:
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 Wrote:

Hi,

How do i write amacrotopopulatecellvaluefrom another sheetbased
uponthedatavalidationlistselection

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile:'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread:'Macrotopopulatecellvaluefrom another sheet
baseduponthe datavalidationlistselection- The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=153354)

'Microsoft Office Help' (http://&quot;http://www.thecodecage.com)

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/sh...d.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 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 he" & 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.

THanks Simon...this helped me out Greatly!

"Simon Lloyd" wrote:


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 Wrote:
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/sh...d.php?t=153354

Microsoft Office Help

.