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 whe
data is detected?

I ask because to do what you want, I think a range will have to b
built from the 2 strings then do the selection based on the "built
string

 

[Guest]

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

 

[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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

Is it possible you can email me an example (maybe just a few worksheets)
(e-mail address removed)