software

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

Simon12345678
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(quot;Sheet1quot;)
Set sh1 = Worksheets(quot;Compilerquot;)
j = 1
Do While sh.Name lt;gt; sh1.Name
Set rng = sh.Range(sh.Range(sh.Range(quot;P3quot;)), _
sh.Range(sh.Range(quot;P4quot;)))
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(quot;P5quot;).Value)
Loop
sh1.Activate
End Sub--
simonsmith
------------------------------------------------------------------------
simonsmith's Profile: www.excelforum.com/member.php...oamp;userid=34235
View this thread: www.excelforum.com/showthread...hreadid=543596
Is the top line quot;12345678quot; 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 quot;ABCDEFGHquot; 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 quot;builtquot;
string.--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543596One 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(quot;P3quot;)), _
sh.Range(sh.Range(quot;P4quot;)))

To get the data by row, try changing the row/column variables
sh1.Cells(j, i).Value = cell.Valuequot;simonsmithquot; wrote:

gt;
gt; Hi,
gt; Someone very kindly made this for me but there is a narly little
gt; problem, example best explains it...
gt;
gt; Lets say this represents two rows of data
gt; 1 would be in position A1
gt; H would be in position H2
gt;
gt; 12345678
gt; abcdefgh
gt;
gt;
gt; The below macro uses what is specified in user input cells P3 and P4 to
gt; know the range of cells to copy.
gt; If the user input cells in the spreadsheet are p3=A1 , p4=h2 it will
gt; copy all (like I want it to)
gt;
gt; BUT
gt;
gt; If p3 = A1 and P4 = b2
gt;
gt; I would like it to copy
gt; 12345678
gt; ab
gt;
gt; but it does not, instead it copies
gt; 12
gt; ab
gt;
gt; I can sort of see why it is doing what it is doing but what it needs to
gt; do (in this example) is copy the full rows (of 8 columns) previous to
gt; P4, then copy the partial row that the P4 specified cell is in!!
gt;
gt; Also macro copies to columns (in the compiler sheet)instead of rows
gt; like the it was in the original sheets
gt;
gt; In case you want to know what the macro is for...
gt; it is for modeling packet data. A system copies data in a packet
gt; (starting a P3) until it reaches a decision point (P4)it will then read
gt; which partial packet to send next (P5),it will then have a new partial
gt; set of data to make up more of the packet (P3,P4) before reaching the
gt; next decision point (P4) then reading where to get the next lot of data
gt; from (p5) etc. The idea behind using the macro is to show that with
gt; different data content, different decisions will be made which will
gt; result in additional data making up the packet.
gt;
gt; Any help really appreciated. The project seems to be easy compared to
gt; VBA magic!!
gt;
gt; Cheers
gt;
gt; Simon
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt; 12345678
gt; abcdefgh
gt;
gt;
gt;
gt;
gt; Sub Macro3()
gt; Dim sh As Worksheet, sh2 As Worksheet
gt; Dim i As Long, j As Long
gt; Dim rng As Range, cell As Range
gt; Set sh = Worksheets(quot;Sheet1quot;)
gt; Set sh1 = Worksheets(quot;Compilerquot;)
gt; j = 1
gt; Do While sh.Name lt;gt; sh1.Name
gt; Set rng = sh.Range(sh.Range(sh.Range(quot;P3quot;)), _
gt; sh.Range(sh.Range(quot;P4quot;)))
gt; i = 0
gt; For Each cell In rng
gt; i = i 1
gt; sh1.Cells(i, j).Value = cell.Value
gt; Next
gt; j = j 1
gt; Set sh = Worksheets(sh.Range(quot;P5quot;).Value)
gt; Loop
gt; sh1.Activate
gt; End Sub
gt;
gt;
gt; --
gt; simonsmith
gt; ------------------------------------------------------------------------
gt; simonsmith's Profile: www.excelforum.com/member.php...oamp;userid=34235
gt; View this thread: www.excelforum.com/showthread...hreadid=543596
gt;
gt;


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(quot;Sheet1quot;)
Set sH2 = Worksheets(quot;Compilerquot;)

Do While sH.Name lt;gt; sH2.Name

Set RngP3 = sH.Range(sH.Range(quot;P3quot;))
Set RngP4 = sH.Range(sH.Range(quot;P4quot;))
Set RngFrom = sH.Range(RngP3.Address amp; _
quot;:hquot; amp; RngP4.Row - 1 amp; quot;,aquot; amp; RngP4.Row _
amp; quot;:quot; amp; 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(quot;P5quot;).Value)
Loop
sH.Activate

End Sub--
mudraker
------------------------------------------------------------------------
mudraker's Profile: www.excelforum.com/member.php...foamp;userid=2473
View this thread: www.excelforum.com/showthread...hreadid=543596
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: www.excelforum.com/member.php...oamp;userid=34235
View this thread: www.excelforum.com/showthread...hreadid=543596
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 quot;A1quot; (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: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=543596
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 hardHi 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: www.excelforum.com/member.php...oamp;userid=34235
View this thread: www.excelforum.com/showthread...hreadid=543596Going 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(quot;Sheet1quot;)
Set sh1 = Worksheets(quot;Compilerquot;)
j = 1
i = 1

Do While sh.Name lt;gt; sh1.Name
Set BeginCell = sh.Range(sh.Range(quot;P3quot;))
Set EndCell = sh.Range(sh.Range(quot;P4quot;))

For Each cell In BeginCell.CurrentRegion.Cells
If (cell.Row gt; BeginCell.Row And cell.Row lt; EndCell.Row) Or _
(cell.Row = BeginCell.Row And cell.Column gt;= BeginCell.Column) Or _
(cell.Row = EndCell.Row And cell.Column lt;= 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(quot;P5quot;).Value)
Loop
sh1.Activate
End Sub

quot;simonsmithquot; wrote:

gt;
gt; Hi JMB, bgeier and mudraker,
gt;
gt; A heads up of what I am trying to do (in case you are getting horribly
gt; confused)...
gt; Each sheet has a matrix of 8 columns and normally 40 rows (these are
gt; made up of ones or zeros). I need the macro to compile (on the compiler
gt; sheet) a matrix that is made up of parts of various matrices from the
gt; other sheets; P3,P4 of each sheet will dictate which cells to copy, p5
gt; will dictate which is the next matrix (sheet) to go to.
gt; (95% of the marco now works - thanks a million for that
gt; Just one narly issue remains...
gt; the compiler sheet does not make a matrix....
gt; The rule is - If a cell or range of cells are copied they need to
gt; (ultimately) be pasted onto the compiler matrix in same postion that
gt; they came from on the sheet of origin.
gt;
gt;
gt;
gt; JMB
gt; The changes you suggested work well, now be either specifying the full
gt; range and partial or full range in P3, P4 respectively it seems to copy
gt; properly!!!!!
gt; As for the pasting by transposing i,j it now copies to rows however I
gt; notice that it does not copy a single row of 8 cells then copying the
gt; next 8 underneath it, rather it will copy the first range specified in
gt; P3 into on long row then do the same for P4. (similar result with i,j
gt; other way around too)
gt;
gt; Would it be easier to get the macro to first paste all cells (as
gt; selected in p3, p4 of each sheet) into 1 column on the compiler sheet
gt; THEN break the column down into 40 rows (one data frame) of 8 cells
gt; (bits). I need to reproduce the orignal form of the data packet (8
gt; columns and normally about 40 rows)
gt; If so how do I do that??
gt;
gt; No stress if its too hard
gt;
gt;
gt; Hi bgeier,
gt;
gt; I tried filling in the sheets with values to overcome this problem
gt; however the same error still occurs
gt;
gt; Thank you both for all your work
gt;
gt; Simon
gt;
gt;
gt; --
gt; simonsmith
gt; ------------------------------------------------------------------------
gt; simonsmith's Profile: www.excelforum.com/member.php...oamp;userid=34235
gt; View this thread: www.excelforum.com/showthread...hreadid=543596
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=34235
View this thread: www.excelforum.com/showthread...hreadid=543596So 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(quot;Sheet1quot;)
Set sh1 = Worksheets(quot;Compilerquot;)
j = 1
i = 1

Do While sh.Name lt;gt; sh1.Name
Set BeginCell = sh.Range(sh.Range(quot;P3quot;))
Set EndCell = sh.Range(sh.Range(quot;P4quot;))

With BeginCell.CurrentRegion
For Each cell In .Offset(1, 1).Resize(.Rows.Count - 1, _
.Columns.Count - 1).Cells
If (cell.Row gt; BeginCell.Row And cell.Row lt; EndCell.Row) Or _
(cell.Row = BeginCell.Row And cell.Column gt;= BeginCell.Column) Or _
(cell.Row = EndCell.Row And cell.Column lt;= 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(quot;P5quot;).Value)
Loop
sh1.Activate
End Sub

quot;simonsmithquot; wrote:

gt;
gt; Hi JMB,
gt; this is pretty much perfect so thank you sooooooooooo much for doing
gt; this.
gt; To reply to your assumption, unfortunately the table has 1-8 across the
gt; row 1 at the top (bit number) and 0 to 39 down the A column on the left
gt; hand side (octet number). So the macro doesnt quite work properly when
gt; compiling unless I remove the octet column on each of about 100
gt; templates I have. What I could do is record a macro to delete that
gt; column at the start then add it back on at the finish!
gt; Would it be easier to copy everything onto a single column in the
gt; compiler first then transpose that it into a 8 row x 40 matrix?
gt;
gt;
gt; Cheers
gt;
gt; Simon
gt;
gt;
gt; --
gt; simonsmith
gt; ------------------------------------------------------------------------
gt; simonsmith's Profile: www.excelforum.com/member.php...oamp;userid=34235
gt; View this thread: www.excelforum.com/showthread...hreadid=543596
gt;
gt;