|
Macro to populate cell value from another sheet based upon the datavalidation list selection
Hi,
How do i write a macro to populate cell value from another sheet based upon the data validation list selection |
Macro to populate cell value from another sheet based upon the datavalidation 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 |
Macro to populate cell value from another sheet based upon thedatavalidation list selection
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 |
Macro to populate cell value from another sheet based upon the datavalidation list selection
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://"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 |
Macro to populate cell value from another sheet based upon thedatavalidation list selection
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://"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 |
Macro to populate cell value from another sheet based upon thedatavalidation list selection
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://"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. |
Macro to populate cell value from another sheet based upon the
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 . |
| All times are GMT +1. The time now is 06:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com