[SOLVED]Adding values to cells(values from different sheet)

Hi, I've sent an Excel file below. If u see carefully at mapview sheet,eg like cell A4,A6,A8 and so on..u can see "1" there. It's a "dummy" value. I want to take the value(total which was created by pivot table eg. "9" in block B01 in Period 0) from blkview sheet and display it,in the cell ("A4") in mapview, the value "9" should display.. and this will apply to all the blks under a certain period(which u can check in the above cells in mapview sheet). Hope u understand.Its urgent.Pls help.Thanks:(

Do I understand that correct?

You have the value"1" in several Cells of Sheet "mapview".
THese values you want to replace with a specific value from the Sheet "blkview" (refrenced by Period and Alhanumeric value like "B01"?

Some questions:
What should happen if the replaced value is "1" again, contnious loop???
When do you want that to happen, or in other words, is there a time when you want to see the original value"1"? If not just let this Cells have the reference to the correspondig Cell in "mapview" (maybe with a If not mapview(Cell)="").

No no... The "1" s are just "dummy" values. U may assume that the cells are empty. U can clear them off.

What about the second question?
When do you want that to happen (the input of the values from the other sheet), or in other words, is there a time when you want to see the original empty cell? If not just let this Cells have the reference to the correspondig Cell in "mapview" (maybe with a If not mapview(Cell)="").

I changed Cells A4 and A8 to show the values from the other sheet.

Yes, I've created a menu button in the sheet I have. Actually it reads some data from a text file and then creates it in a pivot table format.The Maximum is 8 periods in blkview, that y I gave tat to u. If you could make this work,Hopefully i think it should work for other values too... I download to see where u entered the value...YES, u r right... That is how I wat them to be done...Pls help. Im trying for days but am too weak in my programming..thanks.

So you have the code that extracts data from a textrfile and puts it into the sheet "blkview"?

The only thing to do now is change the reference of the cells in "mapview" which should display values from "blkview".
Take my example and change the other cellls too. Soory, pasting won't work, since the offsets are irregular.

Let me know if you need more help

A nice way to show that you'Re done is to ReEdit the original thread and put a "SOLVED" in the topic.

huh....?!! whre is ur example? I can't see ay source codes which I could change from... where is ur codes? pls help..:(

As I understood, you have all the code to put the data from a txt-file to the sheet "blkview", right???????



To put the values from "blkview" to the corresponding cells in "mapview" use the examples in the file I posted (book1.xls).
I changed the cells "A4" and "A8" of "mapview" to show the values of "blkview". Change the other cells according these examples!
No other code is needed! At least to my understanding of your problem!

what do u mean by no code is required...? u mean to ask me to compare the 2 sheets and type in the value one by one?? i've posted this question coz i didn't know how to dynamically reference the cells. the mapview stays the same only the blkview value changes now n then so i need to generate this at a click of a menu command....i doubt u misunderstood me. do u understand now?pls....By the way what do u mean that "U changed the values for "A4" and "A8" "? I don get u ...did u do it programmatically....or jus key in those values? If u did use some logic, then I want some sample codes thats all..thanks.

did you look at the "book1.xls" I attached some posts up?

Look in there at Cell "mapview"A1

The formula is "=If(blkview!B3<>"";blkview!B3;"")"

That way, each time the Cell B3 of "blkview" is changed, the value of A4 is changed too. It will stay empty as long B3 is empty.

If THIS doesn't solve your problem, I do need more explanation of what you need!

Sorry I've nv used the formula bar ...how do u get to do it..? whenever I try to type in the formula there, only the formula appears in the cell....Aah...now ive tried can but it is not how i want it to be done... u c.. sometimes in blkview there will be no B05 or C01 instead will have J12 and the values for it for period one may be 8,9,13, etc... if this blkview changes, then the mapview also change (meaning the GUI like what u've seen will be different), arrangement of blocks etc...Get it?:mad:

To be frank, I don't get it!

As I see it, you have to adjust each Cell-formula manually, since there is no pattern, that EXCEL could use to change to formulas relative.
And for the part :

Quote:

---

sometimes in blkview there will be no B05 or C01 instead will have J12 and the values for it for period one may be 8,9,13, etc... if this blkview changes, then the mapview also change (meaning the GUI like what u've seen will be different), arrangement of blocks etc...Get it?

---

I Don't understand what you are trying to say!

I must admit this is very difficult to understand - I've just seent this post for the first time & had to re-read it a couple of times.

Opus, I think by the sound of it he needs a script to basically create that first sheet - take whatever values are posted on the second sheet and create the tables & values from the first sheet off of this - not an easy 5 min job!

I'll take a look at a sample at home over the next couple of days if this hasn't been answered beforehand...

Hi, Alex
As I understood, he just wanted that "easy" putting values from one sheet to the other (look at the topic)
In his book1.xls, he has this sheet.

But it took some time to make him look closely at the changed book1.xls.

I think his problem is an EXCEL-learning problem and not a VBA-problem.
Read that:

Quote:

---

Sorry I've nv used the formula bar ...how do u get to do it..? whenever I try to type in the formula there, only the formula appears in the cell..

---

I'll try to "talk" him thru that one.

okay, ta ;)

But I guess it'S sleeping time now, at leastr in "his" part of the world ;-)

Please wait guys...I'll get ready what im supposed to and sdend u all a clearer xl file...pls wait...I bet u will understand by the....Just 10 minssspls wait..Thankx

Hurry up,
in here the sun is shining and my officetime is over.
I'll take whatever you have and look at it at home.

OK mapview1 corresponds to blkview1 and mapview2 corresponds to blkview2 and so on... As I've told u these sheets are created dynamically using when a user click a button in the Userform(this is not the problem). Several views are created...Not all the mapviews together.Either mapview1 or mapview2 ormapview3 together with their matching blkview(s).And so I want those values in blkview to be "pasted in mapview as what opus demonstarted..now hope u all understand...thankx. alot 4 ur helps..!

Do you need code to automatically create mapviewX?
Or do you only need the functions filled in that will reference specific cells from an (already created) blkviewX to an (already created) mapviewX ?

the mapview is already created...Won't change. The format of the blkview is the same but the values in it may vary for different "sections"..As u can see the difference in the sample I showed u.. I've sent u 3 types of views rite? Actually there are 7 different types of them.So if it can be done dynamically for at least 2 of the vies I showed u.hopefully it WILL work for the rest..I'm trying it too..But my programming wise...ggggrrhhh.. pls help.

Hi,I took some sleep to think about it.

As I understand your problem/desire now is:

You have a txt-file that contains data which you fill into several "blkview" worksheets, you do that using what??
If you are using code for that already, the easiest thing would be to implement the creating of all "mapview" sheets into this code.


In your example mapview's there are some significant differences:

You have Periods ranging from lower limit "0" or "1" to "8" or "(blank)". Is that correct, or should it be 0-8 for all?

For AktBlk you have Values using one Letter and two digits, what is the maximum number of different letters in one view?
The numbers allways range from "01" counting up. Is there a maximum?

On formatting of mapview:

For each Period you have:

A yellow header block (2 rows, width according number of diffferent letters). This Block holds the Period number ("Period X") in the first cell and in a cellblock a date/time or what(different ones are used)???

Under each of those yellow headers you have for each different letter a 3 column-wide block.
For each number ("A01 ")you 3 Columns and 2 rows in alteranting color.The number is put in the first row,second column.
The corresponding value from blkview should be in second row, first column.
But sometimes the letters go in different blocks??(look at mapview2, is that correct?)

Finally, if you already have some code, please post it, that way I can be of better help.

ok I'M sending u the whole file. When u open xl, u'll find a simple splash screen followed by a dialog.Untouch the values "T and 1" and just click view.U can try this by typing any combination of(T,2),(K,3),(K,4),(K,5),(B,W) or (B,E).(make sure u clear all screensgiven in the menubtn b4 u start searching for another) .The mapview is actually generated using macros.What u have to play around with is under the Show menu->Show Activity. It wil only show the blkview if the mapview is viewable.Try it for the diff terminals n section eg.K, 3 and click View. Im sending some text files.Create a folder on the desktop ,named "CreateAct" and place them in. Create also a folder in C drive named "GetActivity". Now u can see it.PLs hope u will do it asap .Im running out of time:( bY THE WAY FOR UR QUES,i DID THE BLKVIEW USING A PIVOT TABLE..nOT MUCH OF CODES U CAN USE IT FROM THERE...huH...iM NOT ABLE TO SEND THE FILE..SAYS ITS TOO BIG..iM TRYING 2 REMOVE AS MCH S POSS

i'VE REDUCED THE FILE SO MUCH ALREADY...I've no ideahow to send it ....help.. Aft zipping it up, it is 204,800 bytes..btw there's no max or min values inmy proj..can be of any blk iD eg D02 ...only the num of periods is fixed 0-8.. u would have to check/compare only this 2 values and then passthe value in blkview to mapview..Now u see I can't even send mmy file over...hhhmmmm..how do u want it 2 be xplained. me going crazy.:mad:

????
I think I'm loosing it!

If you already have the mapviews created, what else do you need?
Maybe whow to put in Formulas into Cells using VBA???

I think you should rephrase your question, that way you don'T have to send the whole file.

ok oPUS..y TAKE IT IN A VERY COMPLEX WAY? Just assume tat I created the mapview(just talk bout one) by recording a macro. Also assume that I've created the blkview using a pivot table which was extracted from somewhere...ok... Don worry bout what is the max num of the alphanumeric value(it is a location in a yard) in the blkview(column A).As long as you can find the same block in mapview, take the value(eg the "9") and put it in the mapview in the range which u've mentioned earlier... If it is not found(tat happens very very seldom, if the data is corrupted) then do whatever wiit it (like dumping the blk in a text file,btw don do this first).b4 u show the value in the mapview also check the period in the blkview from where the value came from. Look at blkview1 ,period 5,Blk B01.It shows a value of "21", so, this value should be put in mapview1 at range("BJ4") same like the "9" in Range("B4"). My idea(which I do not know how to do) is first to specify a range from mapview to choose from.Eg. If a value is found to be in period 2 in blkview then the value should go somewhere between the range("Y4 : AI32")...this is how I think it should be done.Syntax wise Im not sure..PLease...:confused: :( :mad: If u could show me how to do this, and its working perfectly, I'll try out myself for the rest of the "views" by putting some "condition". You just concentrate on mapview1 and blkview1.Thanks.

I know I'm annoying, but
you say:

Quote:

---

My idea(which I do not know how to do) is first to specify a range from mapview to choose from.Eg. If a value is found to be in period 2 in blkview then the value should go somewhere between the range("Y4 : AI32").

---

I wondering whow are you "specifying", I was thinking you would want to show all values from all blkview-tables in all mapviews instantly?

That way I would put the coding into the creating of the mapview, in the same routine that puts in the AlphaNumerics.

But if you want to trigger the input of the Values after the mapviews already hold the Alphanumerics (like in your example book2.xls), it would be a slight different coding.

Which way, I'm still with you

No, I would like to show values from a single blkview to a mapview one at a time (take it as if Im clicking a command button in the Userform) Just try for one will do.And one thing I want to make it clear with u is, the alphanumeric "values/details" are already there in the mapview.I used a macro to record my actions. U would have noticed that under every block(eg,B05), there are 3 columns of cells rite? I want the values to go into the center(2nd) cell. Thought I ve already mentioned in detail by telling u a sample range. I'm sure I've given plenty of xplanation..hope u would help me out wit tis.Thanks buddy.

I hope that fits to your needs.

VB Code:

Public Sub Input_Values()
'You have mapviewX open  and have selected a range block (one vertical block of 3 columns under a specific Period)
'The yellow header lines are not selected (example a3:c32)
Dim i As Integer
Dim j As Integer
Dim Name As String
Dim ViewNumber As Integer
Dim Period As Integer
Dim BlkName As String
Dim MaxNumber As Integer
'How many Alphanumerics do you have at max??
MaxNumber = 100
Name = ActiveSheet.Name
ViewNumber = CInt(Right(Name, 1))
BlkName = "blkview" & ViewNumber
'Get the Period
'the Period is always mentioned in row 1 in cells furthest left cells that is still yellow
'because not all cells are yellow in Row 1 I check row 2 instead!
'the first Period has to be 0 ALLWAYS!!!!!
For i = Selection.Column To 1 Step -1
    If ActiveSheet.Cells(2, i).Interior.ColorIndex = -4142 Then
        Period = Val(Right(ActiveSheet.Cells(1, i + 1).Value, 1))
        i = 1
    End If
Next i
'The alphnumerics are in the second column in every second row
For i = 1 To Selection.Rows.Count Step 2
    For j = 3 To MaxNumber
        If Sheets(BlkName).Cells(j, 1).Value = ActiveSheet.Cells(i + 2, Selection.Column + 1).Value Then
            'Period+2 is used since the values start in column 2 and Period starts always with 0!!!
            ActiveSheet.Cells(i + 3, Selection.Column + 1).Value = Sheets(BlkName).Cells(j, Period + 2).Value
        End If
    Next j
Next i
End Sub

Oh my God!Opus...what I mean by blkview1,blkview2,blkview3 and so on are examples of the different views(internally).I showed that so that u could understand better coz I blif earlier u had a wrong misunderstanding that the blk views would only show B01,B02,B03....C01,C02,C03,C05...D01,D02....and so on right? I have only 4 sheets in my workbook. They are mapview,snowballview,blkview,and TTview. In a single sheet(blkview) or (mapview) all the diff types of location can be viewed by searching thru a Userform. Of course it wouldn't be logic or would it be saving space if I use diff sheets for diff viewing?? I would reuse it rite by refreshing my page..Get it...I 've tried to put ur codes in Book.xls but no results. In my actual workbook I have type mismatch error for this:ViewNumber = CStr(Right(Name, 1)).
I guess spotted that ur logic was wrong s soon as I saw this:BlkName = "blkview" & ViewNumber
U r trying to tel that ViewNumber is either 1,2, 0r 3 rite??No, I only have one sheet called blkview. I'm 100% sure that when u are coding the logic , u need not care bout the sheet at all EXCEPT when u want to activate them to display. I've only use this code so far in my coding just to activate.Worksheets("blkview").activate r u still confused?? the first time frm ur response i tot u got / understood wat i really want....:confused:

OK, if you only have one blkview and one mapview, So I change my code. Tell me if it still doesn't fir your needs, because I'm still a bit lost.

The code will still take it from a selected block in mapview (3 columns wide, starting with row 3!)

VB Code:

Public Sub Input_Values()
'You have mapview open  and have selected a range block (one vertical block of 3 columns under a specific Period)
'The yellow header lines are not selected (example a3:c32)
Dim i As Integer
Dim j As Integer
Dim Name As String
Dim Period As Integer
Dim BlkName As String
Dim MaxNumber As Integer
'How many Alphanumerics do you have at max??
MaxNumber = 100
Name = ActiveSheet.Name
'Get the Period
'the Period is always mentioned in row 1 in cells furthest left cells that is still yellow
'because not all cells are yellow in Row 1 I check row 2 instead!
'the first Period has to be 0 ALLWAYS!!!!!
For i = Selection.Column To 1 Step -1
    If ActiveSheet.Cells(2, i).Interior.ColorIndex = -4142 Then
        Period = Val(Right(ActiveSheet.Cells(1, i + 1).Value, 1))
        i = 1
    End If
Next i
'The alphnumerics are in the second column in every second row
For i = 1 To Selection.Rows.Count Step 2
    For j = 3 To MaxNumber
        If Sheets("blkview").Cells(j, 1).Value = ActiveSheet.Cells(i + 2, Selection.Column + 1).Value Then
            'Period+2 is used since the values start in column 2 and Period starts always with 0!!!
           [b] ActiveSheet.Cells(i + 3, Selection.Column + 1).Value = Sheets("blkview").Cells(j, Period + 2).Value[/b]
        End If
    Next j
Next i
End Sub

Hi Opus, I wonder whether u got test it b4 u sent the codes? coz im not getting any any result at all.the mapview columns ust stays as blank.

Yes , it's tested.

You need to.
-paste code into a module of your book.xls.
-select a block in mapview starting in row 3 consisting of all 3 colored columns (like A3:C32).
-goto the VBA put the cursor into the Sub Input_Values and click and start the Routine (F5-Key).

That way all values in the selected range are filled in.

Hhhmmm..tats to say that u will have to select the cells fiirst is it before calling the procedure...Alright.Thanks a lot. I'll give it a try .If any problems, don't mind ok..I'll ask u Hope u have also done to check in the period.. Thanks:)

I did make like that, because you said:

Quote:

---

My idea(which I do not know how to do) is first to specify a range from mapview to choose from.

---

But you can easily make a Sub that hands over the selection.range and activates the "mapview", before calling this Sub. Taht way you can automate it for all possible blocks.

If you need further help, just call.

Note: My weekend is starting soon, and

VB Code:

If Weekend=True then
    CodingTime=Empty
     FamilyTime=Max
End If

oPUS...So ur code works such tat if any of the cells are empty while reading in, it stops tranfering data to mapview...I've noticed that. So the following rest of the values are left blank in mapview. Do u know how to check if any of the values in blkview is "" nothing, then put a zero there.And there's a problem there, i guess. bcoz as i hav created blkview using a pivot table, by default the sheet will be protected. So how?

??
I checked that, to my understanding the doesn't stop when comes to an empty cell!
I checked by leaving the Value for B02 Period 0 empty, the values after B02 did get filled in!
If you want to have a "=" instead of an empty cell use the following code(just replave the old line with the new 5 lines)

VB Code:

If Not Sheets("blkview").Cells(j, Period + 2).Value=Empty then
   ActiveSheet.Cells(i + 3, Selection.Column + 1).Value = Sheets("blkview").Cells(j, Period + 2).Value
Else
  ActiveSheet.Cells(i + 3, Selection.Column + 1).Value = 0
End If

You have a problem with a protected sheet? Use:

VB Code:

SheetName.unprotect

Using my code, a protected blkview should be of no problem, since nothing is changed in there???

Hi Opus Where should I put the above 5 lines of code? This code is to check if the blkview has any blank cells withing the pivot table rite? If yes then insert a zero? Y am I getting an error.Another problem in ur original code when I run it, it prompts as "Appllication defined or Object defined error" for this line :-
If Not Sheets("blkview").Cells(j, Period + 2).Value = Empty Then
and "Overflow" in this line:_
For i = 1 To Selection.Rows.Count Step 2
Whats ya the problem?

Ok ...I've attached a sample on blkview and mapview to illustrate how mine works(here I've done it with the help of a userform). When u first open the page, start VB editor and run the form. Click the button on the top first. The first button is the one which creates the blkview using a pivot table. (if u want to try it out several times, unprotect the sheet and delete contents). The second button below it is the one that transfers data to mapview. If u click n see, nothing happens. But lets say, u hav selected the cells in mapview then u run and click the 2nd button, U will notice that the data transfer to only the first block in mapview(furthermore, the following cells below will be empty).I hope u can visualize and undestand better. hope tyo hear from u soon.

Hi,
I can't figure the problems you observed regarding not filled cells or Overflow.
Regarding

Quote:

---

If u click n see, nothing happens. But lets say, u hav selected the cells in mapview then u run and click the 2nd button, U will notice that the data transfer to only the first block in mapview

---

As you told me to do it for:

Quote:

---

My idea(which I do not know how to do) is first to specify a range from mapview to choose from.

---

I made a code for trhe instance that the selection was made before.

However using your new example I did the following:

Adjusted that all Periods will be filled at one click (2nd Button).
The Alphanumeric have to be in 3 columns (15,15 and 8 values) for each Period. The Values itself don't have to have any format.
If a empty cell in blkview is found, "0" is filled into mapview.

I hope that's it now.

THANK YOU SO MUCH FOR UR EFFORT TAKEN THESE DAYS. THANK YOU VERY VERY VERY VERY MUCH.IM REALLY REALLY HAPPY NOW!:D :)

I guess you owe me one, see next time I come to Singapore!

What bugs?, yours or mine?

YOUR SOLUTION DOES 95% OF WATS REQUIRED BUT WHEN I TRIED THAT FOR SOME OTHER VIEW IT SHOWS 0 ,0, 0 IN BLANK SPACES... I KNOW U WON'T UNDERSTAND WAT IM SAYING TSTA Y I SENT HOW IT LOOKS LIKE.BTW I'VE USED THE SOLUTION TO CHANGE FOR THE DIFFERENT TYPES OF INFO. U CAN C TAT THERE ARE ADDITIONAL INFO IN THE 3 CELLS BUT THEY R NOT SO DIRECT LIKE THE BLK VIEW... LOOK AT THOSE ZEROS WHERE THEY ARE NOT SUPPOSED TO BE..(MEANING WHERE THERE'S NO INDICATION OF ANY BLOCK). AND I NEED ZEROS TO BE DISPLAYED IF WHATEVER VIEW IT IS RETRIVEING FRM IS EMPTY....Y IS IT SO?

If you'Re talking about the zeros in the non-colored fields, that'S because the code I send uses 15 doublerows for the first and second block in a Period and 8 for the third.
I think YOU can adjust that easily yourself! Look at the comments!
As for the tripple Zero, I didn't do them!!!, I only filled the Cells that are below the values like "B01"!

yeah I understand what u mean and think i could change it myself...just asked to c whether it was possible to optomize the code so tat it can be reused for diff types of views tats all.. anywy thanks buddy.:cool:

OPus... I've got a new problem here..Not that prev one but similar. I've attached a file u can see.Same thing, in Sheet1, I've got a pivot table listing Periods,TT_iD,and Blks. I want those TT_Ids under a certain blk at a certain period(jus like the one u did) to be display in mapview as Comments. The way it should be shown, I've it in Sheet2, for C13 in period7,B01 at period 1 and B01 at period 0 (samples). I know its quite similar to ur codes but i do not know how to reference cell as wat u did.. Ur standard is quite "high" for me.pls help..Thanks:rolleyes:

Hi
I really think you will into this problem more often, only because you use those "pivot-tables" (what are they, some EXCEL-buildin functions eh?).
Once again the question, will it be during one action that you fill those "pivot-tables" and then do the rest????
IF YES, it would be much easier to do it all at same time, since the referencing would be no problem!!!!

On your question, how to reference in this example:

Do it similarv to the last one:
loop thru
all periods
for all 3 blocks in a period (8 ,8 or 3 values)
if the "Alphanumeric value matches to blk
Get all values of TTId that don't hold "" in the period cell
(the problem in here is you have to take more than one
TTId, the ones following with an empty cell in column
1 are also for this blk)[that the reason for the question
above]

I hope you can do that alone!

If you have problems regarding the exact coding to put comments, just use the build in macro-recorder!!!!

Use a new thread for new questions, the loading time for this threat get'S bigger and bigger!!

ya for ur question..Once again the question, will it be during one action that you fill those "pivot-tables" and then do the rest????
, it is YES..Just like the prev one..

I've tried it but getting application defined object defined error. This is how I changed.

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Name As String
Dim Period As Integer
Dim BlkName As String
Dim MaxNumber As Integer
MaxNumber = 100
Sheets("mapview").Activate
'for Periods 0 to 8
For k = 0 To 8
'First block of alphanumerics (15 Values)
For i = 1 To 15
For j = 3 To MaxNumber
If Sheets("Sheet2").Cells(j, 1).Value = ActiveSheet.Cells(1 + 2 * i, 12 * k + 2).Value Then
If Not Sheets("Sheet2").Cells(j, Period + 2).Value = Empty Then
ActiveSheet.Cells(2 + 2 * i, 12 * k + 1).AddComment
ActiveSheet.Cells(2 + 2 * i, 12 * k + 1).Comment.Visible = False
ActiveSheet.Cells(2 + 2 * i, 12 * k + 1).Comment.Text Text:=Sheets("Sheet2").Cells(j, k + 2).Value 'Text:="SIRAJ:" & Chr(10) & ""
' ActiveSheet.Cells(2 + 2 * i, 12 * k + 2).Value = Sheets("Sheet2").Cells(j, k + 2).Value
' Else
' ActiveSheet.Cells(2 + 2 * i, 12 * k + 2).Value = 0
End If
End If
Next j
Next i

'Second block of alphanumerics (15 Values)
For i = 1 To 15
For j = 3 To MaxNumber
If Sheets("Sheet2").Cells(j, 1).Value = ActiveSheet.Cells(1 + 2 * i, 12 * k + 6).Value Then
If Not Sheets("Sheet2").Cells(j, Period + 2).Value = Empty Then
ActiveSheet.Cells(2 + 2 * i, 12 * k + 5).AddComment
ActiveSheet.Cells(2 + 2 * i, 12 * k + 5).Comment.Visible = False
ActiveSheet.Cells(2 + 2 * i, 12 * k + 5).Comment.Text Text:=Sheets("Sheet2").Cells(j, k + 2).Value 'Text:="SIRAJ:" & Chr(10) & ""
' Else
' ActiveSheet.Cells(2 + 2 * i, 12 * k + 6).Value = 0
End If
End If
Next j
Next i
'Third block of alphanumerics (8 Values)
For i = 1 To 8
For j = 3 To MaxNumber
If Sheets("Sheet2").Cells(j, 1).Value = ActiveSheet.Cells(1 + 2 * i, 12 * k + 10).Value Then
If Not Sheets("Sheet2").Cells(j, Period + 2).Value = Empty Then
ActiveSheet.Cells(2 + 2 * i, 12 * k + 9).AddComment
ActiveSheet.Cells(2 + 2 * i, 12 * k + 9).Comment.Visible = False
ActiveSheet.Cells(2 + 2 * i, 12 * k + 9).Comment.Text Text:=Sheets("Sheet2").Cells(j, k + 2).Value 'Text:="SIRAJ:" & Chr(10) & ""
' Else
' ActiveSheet.Cells(2 + 2 * i, 12 * k + 10).Value = 0
End If
End If
Next j
Next i

Next k

Not accurate though...PLs help me.. thanks. Take a look at the file I've given u as an example to check.I blif this is slightly diff coz the TT_id and theblk names seem to be in the same column in the pivot table rite??(im blur)Thankx:(

In this case I would start totally different.
Let'S assume you only want to make the mapview
I would use the raw data and do for each period and each blk the following
-1- get the data for the cell 1 row below, 1 column before (whatever that is) [ if you have the summ up different other cells, do it in here]
-2- get the data for the cell 1 row below, in the same column (whatever that is) [ if you have the summ up different other cells, do it in here]
-3- get the data for the cell 1 row below, 1 column after (whatever that is) [ if you have the summ up different other cells, do it in here]

For the summing up, do it like
(you are searching for the number of jobs of "SearchedActBlk" in "Period")

VB Code:

for i=1 to NumberofEntriesInRawData
    if RawData(i,RawDataColumn).value= SearchedActBlk
       Summ=Summ+1
    end if
next i

Aiyo... I don't get it..Can u pls edit the codes above and send me ..if it works for one view, i'll try to change the rest to suit my needs..Im not a programmer..me very new to all this.

Try this (not tested yet)

VB Code:

Sub DisplayTTId()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Name As String
Dim Period As Integer
Dim BlkName As String
Dim MaxNumber As Integer
MaxNumber = 100
'for Periods 0 to 8
For k = 0 To 8
    'First block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 2).Value Then
                'Count the rows that are for the same "From_blk"
                For l = j + 1 To j + 20
                    TTID_Number = 1
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                For l = 0 To TTID_Number - 1
                If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text Text:=Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text & Sheets("Sheet2").Cells(j, k + 2).Value
                Next l
            End If
        Next j
    Next i
    'Second block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 2).Value Then
                'Count the rows that are for the same "From_blk"
                For l = j + 1 To j + 20
                    TTID_Number = 1
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                For l = 0 To TTID_Number - 1
                If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Text Text:=Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text & Sheets("Sheet2").Cells(j, k + 2).Value
                Next l
            End If
        Next j
    Next i
    'Third block of alphanumerics (8 Values)
    For i = 1 To 8
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 2).Value Then
                'Count the rows that are for the same "From_blk"
                For l = j + 1 To j + 20
                    TTID_Number = 1
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                For l = 0 To TTID_Number - 1
                If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Text Text:=Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text & Sheets("Sheet2").Cells(j, k + 2).Value
                Next l
            End If
        Next j
    Next i
Next k
End Sub

Opus.i assumed ur Sheet2 was the mapview and Sheet1 was the table...correct? For what I' run n see was there is one whole line of comments in the mapview(lets say the whole line 2).They are showing the blk details...B01,B03 etc... and some comments are empty... The TT_id is not shown at all.I've sent the file..Run the Userform and see..

with a userform for testing...

Sorry for the errors,
this time with testing

VB Code:

Sub DisplayTTId()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Name As String
Dim Period As Integer
Dim BlkName As String
Dim MaxNumber As Integer
Dim CommentText As String
MaxNumber = 100
'for Periods 0 to 8
For k = 0 To 8
    'First block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 2).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
    'Second block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 6).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
    'Third block of alphanumerics (8 Values)
    For i = 1 To 8
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 10).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, Period + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
Next k
End Sub

It works with your Comments example, so its "sheet1" and so on!

Opus, it works better now BUT certain comments are not shown at all.U check properly if u dun blif me... Lets say for BLK C13 in period 2, there should be comments on 2 TT_ID but when I check the mapview, not even the comment sign is there.. And there are alot of bugs like tat...If u didn't get the picture of wat im really doing, let me tel u now.. Actually if there is a value in a cell,let's say 3, tat means the comment should show 3 TT_ids ... But from watever i tested and twisted wit my program, a TT_id comment is also visible ven though there's no value at all in the cell... Only u r capable of helping me..If u need anything for me to upload, do tel me or what u really want. Iwanted to send the whole file but its to large. I'll try to get a better pic of wat it is like if u want..Thanks for helping.

looks like, my "real" duty is interfering!

VB Code:

Sub DisplayTTId()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim Name As String
Dim BlkName As String
Dim MaxNumber As Integer
Dim CommentText As String
MaxNumber = 100
'for Periods 0 to 8
For k = 0 To 8
    'First block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 2).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, k + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 1).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
    'Second block of alphanumerics (15 Values)
    For i = 1 To 15
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 6).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, k + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 5).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
    'Third block of alphanumerics (8 Values)
    For i = 1 To 8
        For j = 3 To MaxNumber
            If Sheets("Sheet1").Cells(j, 1).Value = Sheets("Sheet2").Cells(1 + 2 * i, 12 * k + 10).Value Then
                'Count the rows that are for the same "From_blk"
                TTID_Number = 1
                For l = j + 1 To j + 20
                    If Sheets("Sheet1").Cells(l, 1).Value = Sheets("Sheet1").Cells(j, 1).Value & " Total" Then TTID_Number = l - j
                Next l
                CommentText = ""
                Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).ClearComments
                For l = 0 To TTID_Number - 1
                    If Not Sheets("Sheet1").Cells(j + l, k + 3).Value = Empty Then
                        CommentText = CommentText & Sheets("Sheet1").Cells(j + l, 2).Value & Chr(10)
                    End If
                Next l
                If Not CommentText = "" Then
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).AddComment
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Visible = False
                    Sheets("Sheet2").Cells(2 + 2 * i, 12 * k + 9).Comment.Text Text:=CommentText
                End If
            End If
        Next j
    Next i
Next k
End Sub

Thanks a lot Opus..It works..U r angry with me? Am I troubling u too much.? U sound angry when u said ur "real " work was interfered..I know n understand the much of effort u've put into this...Thanks alot buddy.:)