Macro to populate cell value from another sheet based upon the datavalidation list selection

H

Hasan

Hi,

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

Simon Lloyd

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)
 
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
 
S

Simon Lloyd

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

Hasan;557248 said:
On Nov 12, 2:20*pm, Simon Lloyd <[email protected]
wrote





Microsoft Office Discussion' (http://www.thecodecage.com)

-----------------------------------------------------------------------
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
based upon the datavalidation list selection - The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=153354


I have a data validation list in Sheet1.A1:A2000 where source of dat
validation list is Sheet3.A1:A2000

Depending upon the selection from the validation list in Sheet1 I wan
the other columns(B,D,F,G) in Sheet1 to populate its correspondin
values in Sheet3.Column C,F,G,

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
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:
Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
- 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.

Ask a Question

Top