Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Hi, Someone very kindly made this for me but there is a narly little problem, example best explains it... Lets say this represents two rows of data 1 would be in position A1 H would be in position H2 12345678 abcdefgh The below macro uses what is specified in user input cells P3 and P4 to know the range of cells to copy. If the user input cells in the spreadsheet are p3=A1 , p4=h2 it will copy all (like I want it to) BUT If p3 = A1 and P4 = b2 I would like it to copy 12345678 ab but it does not, instead it copies 12 ab I can sort of see why it is doing what it is doing but what it needs to do (in this example) is copy the full rows (of 8 columns) previous to P4, then copy the partial row that the P4 specified cell is in!! Also macro copies to columns (in the compiler sheet)instead of rows like the it was in the original sheets In case you want to know what the macro is for... it is for modeling packet data. A system copies data in a packet (starting a P3) until it reaches a decision point (P4)it will then read which partial packet to send next (P5),it will then have a new partial set of data to make up more of the packet (P3,P4) before reaching the next decision point (P4) then reading where to get the next lot of data from (p5) etc. The idea behind using the macro is to show that with different data content, different decisions will be made which will result in additional data making up the packet. Any help really appreciated. The project seems to be easy compared to VBA magic!! Cheers Simon 12345678 abcdefgh Sub Macro3() Dim sh As Worksheet, sh2 As Worksheet Dim i As Long, j As Long Dim rng As Range, cell As Range Set sh = Worksheets("Sheet1") Set sh1 = Worksheets("Compiler") j = 1 Do While sh.Name < sh1.Name Set rng = sh.Range(sh.Range(sh.Range("P3")), _ sh.Range(sh.Range("P4"))) i = 0 For Each cell In rng i = i + 1 sh1.Cells(i, j).Value = cell.Value Next j = j + 1 Set sh = Worksheets(sh.Range("P5").Value) Loop sh1.Activate End Sub -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Is the top line "12345678" always complete? In other words will the bit positions (I am assuming they are bits based on your eplanation) always be present and the bottom line "ABCDEFGH" only be present when data is detected? I ask because to do what you want, I think a range will have to be built from the 2 strings then do the selection based on the "built" string. -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Simon I think this gives you what you require Sub Macro() Dim sH As Worksheet Dim sH2 As Worksheet Dim i As Long Dim j As Long Dim RngP3 As Range Dim RngP4 As Range Dim RngFrom As Range Set sH = Worksheets("Sheet1") Set sH2 = Worksheets("Compiler") Do While sH.Name < sH2.Name Set RngP3 = sH.Range(sH.Range("P3")) Set RngP4 = sH.Range(sH.Range("P4")) Set RngFrom = sH.Range(RngP3.Address & _ ":h" & RngP4.Row - 1 & ",a" & RngP4.Row _ & ":" & Cells(RngP4.Row, RngP4.Column).Address) i = 0 For Each cell In RngFrom i = i + 1 sH.Cells(i, j).Value = cell.Value Next j = j + 1 Set sH = Worksheets(sH.Range("P5").Value) Loop sH.Activate End Sub -- mudraker ------------------------------------------------------------------------ mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Thanks for your help and to respond.... Bgeier, There is always a 0 or 1 in the fields. P3/ p4 will determine whether some or all of these are wanted. The macro looks a a template of 0 and 1s. Different templates will have different bits that need to be either copied or ignored and go onto the next sheet. Incidentally some bits in the bit pattern are used to derive what the next sheet name will be in P5 (I will do this later on with a Vlookup once the code works 100%) E.g. the forst 4 bits 1001 pattern could mean make P5 = SHEET 3, 1011 could mean make P5= Sheet 2. I hope this helps you understand what I am trying to do. Thanks for your help. JMB Thank you too, I will try this in the morning, much appreciated. MUDraker. Wow you are outstanding, thanks for all the code. I tried it out, but get error 1004 application defined or object defined error. I F8 stepped through it, it gets to sH.Cells(i, j).Value = cell.Value then errors, so does not get to loop. Do you know what it might be? i ran the previous macro to check I hadnt screwed up some values on the sheets somewhere and it seem to run as before Thanks again Cheers Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
I will look at this further to see what I can come up with. As to the 1004 error, it may be because one of your variables (i,j) is either blank or 0. I suspect it is because it is a 0. The line is basically saying look in cells "A1" (for example) which is the same as cells(1,1) or i,j. Clear as mud??? Sorry, cannot think of a clearer way of stating it. -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Hi JMB, bgeier and mudraker, A heads up of what I am trying to do (in case you are getting horribly confused)... Each sheet has a matrix of 8 columns and normally 40 rows (these are made up of ones or zeros). I need the macro to compile (on the compiler sheet) a matrix that is made up of parts of various matrices from the other sheets; P3,P4 of each sheet will dictate which cells to copy, p5 will dictate which is the next matrix (sheet) to go to. (95% of the marco now works - thanks a million for that:) Just one narly issue remains... the compiler sheet does not make a matrix.... The rule is - If a cell or range of cells are copied they need to (ultimately) be pasted onto the compiler matrix in same postion that they came from on the sheet of origin. JMB The changes you suggested work well, now be either specifying the full range and partial or full range in P3, P4 respectively it seems to copy properly!!!!! As for the pasting by transposing i,j it now copies to rows however I notice that it does not copy a single row of 8 cells then copying the next 8 underneath it, rather it will copy the first range specified in P3 into on long row then do the same for P4. (similar result with i,j other way around too) Would it be easier to get the macro to first paste all cells (as selected in p3, p4 of each sheet) into 1 column on the compiler sheet THEN break the column down into 40 rows (one data frame) of 8 cells (bits). I need to reproduce the orignal form of the data packet (8 columns and normally about 40 rows) If so how do I do that?? No stress if its too hard Hi bgeier, I tried filling in the sheets with values to overcome this problem however the same error still occurs Thank you both for all your work Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
One option you can try is to enter each range in P3 and P4
P3 = A1:H1 P4 = A2:B2 Then define your range as Set rng = Union(sh.Range(sh.Range("P3")), _ sh.Range(sh.Range("P4"))) To get the data by row, try changing the row/column variables sh1.Cells(j, i).Value = cell.Value "simonsmith" wrote: Hi, Someone very kindly made this for me but there is a narly little problem, example best explains it... Lets say this represents two rows of data 1 would be in position A1 H would be in position H2 12345678 abcdefgh The below macro uses what is specified in user input cells P3 and P4 to know the range of cells to copy. If the user input cells in the spreadsheet are p3=A1 , p4=h2 it will copy all (like I want it to) BUT If p3 = A1 and P4 = b2 I would like it to copy 12345678 ab but it does not, instead it copies 12 ab I can sort of see why it is doing what it is doing but what it needs to do (in this example) is copy the full rows (of 8 columns) previous to P4, then copy the partial row that the P4 specified cell is in!! Also macro copies to columns (in the compiler sheet)instead of rows like the it was in the original sheets In case you want to know what the macro is for... it is for modeling packet data. A system copies data in a packet (starting a P3) until it reaches a decision point (P4)it will then read which partial packet to send next (P5),it will then have a new partial set of data to make up more of the packet (P3,P4) before reaching the next decision point (P4) then reading where to get the next lot of data from (p5) etc. The idea behind using the macro is to show that with different data content, different decisions will be made which will result in additional data making up the packet. Any help really appreciated. The project seems to be easy compared to VBA magic!! Cheers Simon 12345678 abcdefgh Sub Macro3() Dim sh As Worksheet, sh2 As Worksheet Dim i As Long, j As Long Dim rng As Range, cell As Range Set sh = Worksheets("Sheet1") Set sh1 = Worksheets("Compiler") j = 1 Do While sh.Name < sh1.Name Set rng = sh.Range(sh.Range(sh.Range("P3")), _ sh.Range(sh.Range("P4"))) i = 0 For Each cell In rng i = i + 1 sh1.Cells(i, j).Value = cell.Value Next j = j + 1 Set sh = Worksheets(sh.Range("P5").Value) Loop sh1.Activate End Sub -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Hi JMB, this is pretty much perfect so thank you sooooooooooo much for doing this. To reply to your assumption, unfortunately the table has 1-8 across the row 1 at the top (bit number) and 0 to 39 down the A column on the left hand side (octet number). So the macro doesnt quite work properly when compiling unless I remove the octet column on each of about 100 templates I have. What I could do is record a macro to delete that column at the start then add it back on at the finish! Would it be easier to copy everything onto a single column in the compiler first then transpose that it into a 8 row x 40 matrix? Cheers Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
So you have a top and left header for each table? We can easily offset and
resize the region the macro loops through. Sub Test2() Dim sh As Worksheet, sh2 As Worksheet Dim i As Long, j As Long Dim BeginCell As Range Dim EndCell As Range Dim cell As Range Set sh = Worksheets("Sheet1") Set sh1 = Worksheets("Compiler") j = 1 i = 1 Do While sh.Name < sh1.Name Set BeginCell = sh.Range(sh.Range("P3")) Set EndCell = sh.Range(sh.Range("P4")) With BeginCell.CurrentRegion For Each cell In .Offset(1, 1).Resize(.Rows.Count - 1, _ .Columns.Count - 1).Cells If (cell.Row BeginCell.Row And cell.Row < EndCell.Row) Or _ (cell.Row = BeginCell.Row And cell.Column = BeginCell.Column) Or _ (cell.Row = EndCell.Row And cell.Column <= EndCell.Column) Then sh1.Cells(i, j).Value = cell.Value If j = 8 Then j = 1 i = i + 1 Else j = j + 1 End If End If Next cell End With Set sh = Worksheets(sh.Range("P5").Value) Loop sh1.Activate End Sub "simonsmith" wrote: Hi JMB, this is pretty much perfect so thank you sooooooooooo much for doing this. To reply to your assumption, unfortunately the table has 1-8 across the row 1 at the top (bit number) and 0 to 39 down the A column on the left hand side (octet number). So the macro doesnt quite work properly when compiling unless I remove the octet column on each of about 100 templates I have. What I could do is record a macro to delete that column at the start then add it back on at the finish! Would it be easier to copy everything onto a single column in the compiler first then transpose that it into a 8 row x 40 matrix? Cheers Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Little problem with this code...
Hey JMB, you are the man, but boy you must be getting sick of this! What is happening now is when I test the compiling doesn't quite "interleave" properly. It seems to copy and position the specified cells correctly from the first sheet, but when it finds P5 and then goes to the next sheet it doesnt seem to copy exactly what is specified in that next sheet's P3 and P4. It seems to copy a few cells earlier, the required cells and a few later ones ie on the next sheet it copies cells within the matrix in additiona to what is specified in P3, P4. I prob didnt say but for the reproduced matrix, it will be always made up of either 320 cells (or 160), there will be no gaps throughout the matrix i.e. the where P4 ends on one sheet P3 will start on the subsequent sheet so that there is continuous interleaving from one sheet to the next of the partial or whole octets of data, which in turn comprise the compiled 40 octet frame of data Would it be easier to go to each sheet and copy the compiler to just make one 320 cell column (from 8 columns and 40 rows from parts of the various sheets) then change that column into a matrix?? Don't stress about it, I will figure something out. I think that when I am testing that occasionally the compiler matrix does work properly when I specific certain combos of P3,P4 but will get to the bottom of this in the morning Thanks for all your help you have been awesome Best Regards Simon -- simonsmith ------------------------------------------------------------------------ simonsmith's Profile: http://www.excelforum.com/member.php...o&userid=34235 View this thread: http://www.excelforum.com/showthread...hreadid=543596 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
regular code module | Excel Discussion (Misc queries) | |||
can you entre a bar code into a excel cell? | Excel Worksheet Functions | |||
VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Worksheet Functions | |||
Concatinate a filename | Excel Discussion (Misc queries) |