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

[ITboy]

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

[opus]

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)="").

[ITboy]

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

[opus]

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)="").

[opus]

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

[ITboy]

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.

[opus]

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.

[opus]

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.

[ITboy]

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

[opus]

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!

[ITboy]

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.

[opus]

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!

[ITboy]

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?

[opus]

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 :

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!

[alex_read]

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...

[opus]

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:

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.

[alex_read]

okay, ta

[opus]

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

[ITboy]

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

[opus]

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.

[ITboy]

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..!

[opus]

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 ?

[ITboy]

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.

[opus]

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.

[ITboy]

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

[ITboy]

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.

[opus]

????
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.

[ITboy]

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... 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.

[opus]

I know I'm annoying, but
you say:

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

[ITboy]

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.

[opus]

I hope that fits to your needs.

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

[ITboy]

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....

[opus]

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!)

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

[ITboy]

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.

[opus]

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.

[ITboy]

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

[opus]

I did make like that, because you said:

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

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

[ITboy]

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?

[opus]

??
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)

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:

SheetName.unprotect

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

[ITboy]

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?