swtbeb4lyfe43
Board Regular
- Joined
- May 1, 2009
- Messages
- 62
Hi,
I need to do something complicated & would appreciate your help.
I'm going to type in a value ("Alias") in a cell on worksheet 1 ("Assessment"). I want this value to look for the matching value in Column B of worksheet 2 ("main") in the same workbook. This Column B contains a huge list of possible values that may match.
Once it finds the row of this matching value in Column B (say, it's in row 55), I want to take all existing values to the right, but only from Column L through X, then paste/transpose this string of values into vertical order under the original "Alias" cell on worksheet 1 ("Assessment").
On worksheet 2 ("main"), for each row there is a different number of values between Column L thru X, so I want it to count how many values there actually are & just paste/transpose those.
I can further explain if needed. Below is a function that does something similar, but I need to find out how to fit it to this:
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cvictoril%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-fareast-language:EN-US;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Sub TransposeColumnEqual()
With Worksheets("Guidelines")
Set c = .Cells.Find(What:="Field Description", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
Set c = Range(c.Offset(1, 0), c.End(xlDown))
numparams = c.Count
End With
With Worksheets("TW")
.Activate
For i = 1 To numparams
.Cells(1, i).FormulaR1C1 = "=Guidelines!R" & c.Row - 1 + i & "C" & c.Column
Next i
End With
End Sub
Sub TransposeParamsEqual()
With Worksheets("Guidelines")
Set c = .Cells.Find(What:="Specified Parameter", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
Set c = Range(c.Offset(1, 0), c.End(xlDown))
numparams = c.Count
End With
With Worksheets("TW")
.Activate
For i = 1 To numparams
.Cells(2, i).FormulaR1C1 = "=Guidelines!R" & c.Row - 1 + i & "C" & c.Column
Next i
End With
End Sub
I need to do something complicated & would appreciate your help.
I'm going to type in a value ("Alias") in a cell on worksheet 1 ("Assessment"). I want this value to look for the matching value in Column B of worksheet 2 ("main") in the same workbook. This Column B contains a huge list of possible values that may match.
Once it finds the row of this matching value in Column B (say, it's in row 55), I want to take all existing values to the right, but only from Column L through X, then paste/transpose this string of values into vertical order under the original "Alias" cell on worksheet 1 ("Assessment").
On worksheet 2 ("main"), for each row there is a different number of values between Column L thru X, so I want it to count how many values there actually are & just paste/transpose those.
I can further explain if needed. Below is a function that does something similar, but I need to find out how to fit it to this:
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cvictoril%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-fareast-language:EN-US;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Sub TransposeColumnEqual()
With Worksheets("Guidelines")
Set c = .Cells.Find(What:="Field Description", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
Set c = Range(c.Offset(1, 0), c.End(xlDown))
numparams = c.Count
End With
With Worksheets("TW")
.Activate
For i = 1 To numparams
.Cells(1, i).FormulaR1C1 = "=Guidelines!R" & c.Row - 1 + i & "C" & c.Column
Next i
End With
End Sub
Sub TransposeParamsEqual()
With Worksheets("Guidelines")
Set c = .Cells.Find(What:="Specified Parameter", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
Set c = Range(c.Offset(1, 0), c.End(xlDown))
numparams = c.Count
End With
With Worksheets("TW")
.Activate
For i = 1 To numparams
.Cells(2, i).FormulaR1C1 = "=Guidelines!R" & c.Row - 1 + i & "C" & c.Column
Next i
End With
End Sub