Little problem with this code...

[simonsmith]

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

[bgeier]

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.

[JMB]

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

[mudraker]

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

[simonsmith]

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

[bgeier]

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.

[simonsmith]

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

[JMB]

Going back to your original macro, with just a beginning cell reference in P3
and the ending cell reference in P4, this will go through every cell in the
table (I'm assuming these tables are bounded by an empty column and row - as
I'm using CurrentRegion to go through all of the cells in the table) and test
the Row and Column to see if the cell is between your begin and end range.
On the Compiler sheet, the data forms an 8 column matrix beginning in A1. Is
this closer to what you're after?

Sub Test()
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"))

For Each cell In BeginCell.CurrentRegion.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

Set sh = Worksheets(sh.Range("P5").Value)
Loop
sh1.Activate
End Sub

"simonsmith" wrote:

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

[simonsmith]

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

[JMB]

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

[simonsmith]

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

[JMB]

Is it possible you can email me an example (maybe just a few worksheets)
[email protected]

"simonsmith" wrote:

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