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
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:
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.
Last edited by opus; Jul 18th, 2007 at 02:51 AM.
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button Wait, I'm too old to hurry!
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"!
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button Wait, I'm too old to hurry!
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.
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
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!!
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button Wait, I'm too old to hurry!
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
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button Wait, I'm too old to hurry!
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.
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
You're welcome to rate this post!
If your problem is solved, please use the Mark thread as resolved button Wait, I'm too old to hurry!
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..
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.
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.
Opus..1 more pls.. I've used ur above codes to display another comments(on Vessel names) in the 2nd cell(prev u did was for the 1st cells).I did make changes to the codes. However, when I run into that procedure, my app gets "hang" I guess something is wrong wit the code when I changed it? I can't figure out. I'll send u the pivot table Im using now(it's the same as prev one).Hope u do have the Excel sheet.I've placed the code in a module(simply codes, this file dont run)Thanks to solve my bugs.Pls..
ooooooohhh! why not, if u have the time to help me? U mean it will display all three fields at one go for the same blk against the periods? interesting... though all these works, actually im quite worried with my long chunky code(ugly) and wish to optimize them nicely.. Jus now ur solution hanged too..but dun worry.I've fixed that problem using Application.ScreenUpdating=False I think too tat tis thread is too long already.When u reply to me can u send me a private msg on wat ur thread title is about so tat i can get back to u if u have the solution or something else we can share..Thanks buddy
Reply With Quote
