Data Block Code

[SUSAN BAXTER]

Hi


I was looking for help to create some data block code. Whats really important is the ability to add more data blocks (anywhere on my sheet) over time and that's why I have chosen data blocks (hope I’m correct). I have found some code and could include it in the post if that will save some time, please advise.


I have two sets of data blocks as an example:


1^stset is B6 to E17 (so 4 columns over and 12 rows down), column B is formatted as text and C/D/E is formatted as numbers. The second data block, is where the data source is located in a true event and are located at cells N6 to P17 (so only 3 columns over and 12 rows down), columns C/D/E are formatted as numbers.


2^nd set is H6 to K10 (so 4 columns over and 4 rows down), column H is formatted as text and I/J/K is formatted as numbers. The second data block, is where the data source is located in a true event and are located in a true event and are located at cells N22 to P25 (so 3 columns over and 4 rows down), columns I/J/K are formatted as numbers.


The code would search the range B6:B12,H6:H10 for any sets (9-9, 11-1, 1-1 etc.) of numbers. As an example it finds 9-3 in cell B8 which would be a true result. The code would then (I’m sure how to write this up so please advise if it’s not clear) would have to determine that the true result was on the 3^rd row down and search the data block N6:P17 to find the third row down which would be N8 and write the numbers to cells C8, O8 and write the numbers to cells D8, P8 and write the numbers to cells E8. The code would continue with the rest of the range to find any other true events and if none end.

Thanks so much for any help/advice.





<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[SUSAN BAXTER]

<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[SUSAN BAXTER]

So I just have to change the cell locations and it should work. I'll try.

 

[James006]

So I just have to change the cell locations and it should work. I'll try.

Strange you are NOT using this macro for its main advantage ...

The code was designed specifically for this flexibility ...!!!

It allows you to have your Data Blocks located wherever you want ...

You only need to adjust the ranges accordingly ... inside the macro ...

 

[SUSAN BAXTER]

Hi James

After trying the code I got it to work but I need to add say another 20 data blocks and maybe more in the future. Is there a way to make this easier. So far I can't get one more added data block to work but I may have to play with it some more.
Thanks again

 

[James006]

Hi,

Can you describe where your 20 data blocks would be located ...

And ... more importantly ...

Can you confirm that each time you will be dealing with these 20 data blocks, they could be located anywhere in in the sheet ....

 

[SUSAN BAXTER]

Hi James

I can give you the locations and they would not move but I may need to add new ones in the future. Are you thinking we can make a change to the code so that would easier to me to do now and in the future?
Thanks again for your help.



<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[James006]

Hi,

Without the exact locations of all your data blocks ... difficult for me to tell you if modifying the code is feasible or not ...

But now ... you are saying none of the data blocks will be moving ...

Can you confirm your data structure is established once for all ...

 

[SUSAN BAXTER]

The sample cell location that I gave in my post has to change. Unless I’m wrong it was my understanding that cell locations could be changed and the code would continue to work. If code searches a range, say A1:A10 and it works but then I change it to W33:W89 I thought it would continue to work or does it depend on what else the code is doing to perform that action?
Maybe I have to change my approach to sheet layout and other code/formulas that I was planning. As an example if I locate the search in say A10:A100 and I put other data in the A/B/C/D etc. columns above the search range that it may possibly affect the code for the search. Is there a term when coding to avoid this from happening? What do I ask to make it cell specific code, if that makes sense?
Back to your question. I can confirm that the data structure is established and will not be moving.
<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[James006]

The sample cell location that I gave in my post has to change. Unless I’m wrong it was my understanding that cell locations could be changed and the code would continue to work. If code searches a range, say A1:A10 and it works but then I change it to W33:W89 I thought it would continue to work or does it depend on what else the code is doing to perform that action?
Maybe I have to change my approach to sheet layout and other code/formulas that I was planning. As an example if I locate the search in say A10:A100 and I put other data in the A/B/C/D etc. columns above the search range that it may possibly affect the code for the search. Is there a term when coding to avoid this from happening? What do I ask to make it cell specific code, if that makes sense?
Back to your question. I can confirm that the data structure is established and will not be moving.
<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

!!!!!!

Read your message FIVE times ... ?????

Feel completely lost in the fog ...

 

[SUSAN BAXTER]

My apologies. I misunderstood what data structure means in VBA. My exact locations for my 20 data blocks would be as follows:
Data Blocks
Set rng1 = Range("D22:I36")

Set rng2 = Range("J22:O36")
Set rng3 = Range("P22:U36")
Set rng4 = Range("V22:AA36")
Set rng5 = Range("AB22:AG36")
Set rng6 = Range("AH22:AM36")
Set rng7 = Range("AN22:AS36")
Set rng8 = Range("AT22:AY36")
Set rng9 = Range("AZ22:BE36")
Set rng10 = Range("BF22:BK36")



Reference Blocks

Set rng11 = Range("BN22:BQ29")

Set rng12 = Range("BR22:BU36")
Set rng13 = Range("BV22:BY36")
Set rng14 = Range("BZ22:CC36")
Set rng15 = Range("CD22:CG36")
Set rng16 = Range("CH22:CK36")
Set rng17 = Range("CL22:CO36")
Set rng18 = Range("CP22:CS36")
Set rng19 = Range("CT22:CW36")
Set rng20 = Range("CY22:DA36")

The data structure is established and does not need to be changed.


Hope I got it right this time. This code looks like a lot of work went into it and so I hope we can use it.
<style type="text/css">p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>