123raajesh
New member
- Joined
- May 1, 2014
- Messages
- 7
- Reaction score
- 0
- Points
- 0
I have this workbook which contains 3 sheets as per image. Googled at lot...
1) User uses drop downs in Sheet 1, and enters the Variables or some data.
2) In Sheet 2, the headers are captured. As per down down selection in Sheet 1 by the user.
3) In Sheet 3, the user enters details for the corresponding drop down value of Sheet 1. The details entered in Sheet 3 are not the same in Sheet 1 Variables. Those are different as mentioned in image. And the details entered in Sheet 3 varies for respective header values, there is no definite length. May observe the same in image. Yes, only one column is used in Sheet 3.
4) Now in Sheet 2, need to get the values entered in Sheet 3.
For example: 1 to 5 values of JAVA_1 i.e code to code4, just above JAVA_2 must be captured or extracted from Sheet 3 to Sheet 2 if header value is entered. If not should be blank as in image. To make it understandable just mentioned count to the left values, marked in blue block. Otherwise its blank, when find ###, after code10 of JAVA_3 will have 3 ###.
5) The header values JAVA_1 to JAVA_5 and the details entered in Sheet 3 for respective header also varies as per user requirement.
Could I know how to achieve this in simplest way. Possibly without VB. Anyways is fine.
I was thought this can be achieved by extracting values between 2 match patterns. Like between JAVA_1 to JAVA_2. But not sure how to achieve this.
I tried this macro but its lengthy and not dynamic.
1) User uses drop downs in Sheet 1, and enters the Variables or some data.
2) In Sheet 2, the headers are captured. As per down down selection in Sheet 1 by the user.
3) In Sheet 3, the user enters details for the corresponding drop down value of Sheet 1. The details entered in Sheet 3 are not the same in Sheet 1 Variables. Those are different as mentioned in image. And the details entered in Sheet 3 varies for respective header values, there is no definite length. May observe the same in image. Yes, only one column is used in Sheet 3.
4) Now in Sheet 2, need to get the values entered in Sheet 3.
For example: 1 to 5 values of JAVA_1 i.e code to code4, just above JAVA_2 must be captured or extracted from Sheet 3 to Sheet 2 if header value is entered. If not should be blank as in image. To make it understandable just mentioned count to the left values, marked in blue block. Otherwise its blank, when find ###, after code10 of JAVA_3 will have 3 ###.
5) The header values JAVA_1 to JAVA_5 and the details entered in Sheet 3 for respective header also varies as per user requirement.
Could I know how to achieve this in simplest way. Possibly without VB. Anyways is fine.
I was thought this can be achieved by extracting values between 2 match patterns. Like between JAVA_1 to JAVA_2. But not sure how to achieve this.
I tried this macro but its lengthy and not dynamic.
Code:
Range("A1").Select
Cells.Find(What:="JVM_1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Cells.Find(What:="save", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Range("C9").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Cut
Sheets("Sheet5").Select
Cells.Find(What:="JVM_1", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Range("B3").Select
Range("B2").Select
ActiveSheet.Paste
Range("B1").Select
Sheets("Sheet4").Select
Selection.Find(What:="JVM_2", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Range("C8").Select
Cells.Find(What:="JVM_2", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
