Solved: Copy range when its not zero

[Ugur]

Here's my problem:

There're 3columns: column A , column B and column C

Column A is filled mostly with 0(zeros) (e.g. from A1 to A500 I have 0). But A50 is 1.

Column B is filled with different data

Now I want to do this:
Everytime when its not zero in colum A (in this case A50 is not "0"), copy range B25:B75 to C1


thanks in advance: pray2:

[Bob Phillips]

So do you copy B25:B75 to C1 regardless of where the non-zero cell is,maybe many times?

[Ugur]

Its not the answer to my questions.:hi:

[lucas]

Ugur, I think you need to clarify your question.

[Ugur]

Am I wrongly hit to quotation site? :S

[Bob Phillips]

Its not the answer to my questions.:hi:

No, it is a question ABOUT your question, because your explanation leaves loose ends.

[Ugur]

Which part needs to be explained more?

[lucas]

see post #2

[Ugur]

Sorry I have been missing 1st lines in your posts :S

[Ugur]

Yes, for the case of A50, I need to copy B25:B75 to C1:C51. That is, I'm copying 51 cells around B50.

Then let's say the next non-zero cell in column A is A125. Then I need to copy B100:B150 to C52:C102. So on and so forth: every time there's a non-zero cell in A, switch to the corresponding row in B and copy 51 cells around that row in B to the end of C.

[Ugur]

In fact, I don't really need to copy B25:B75, but more precisely, B25:B47 and B53:B75. That is, skip B50 and two cells to each of its immediate sides. This gives us 46 cells to copy to C1:C46 (instead of C1:C51). (So B25:B47 goes to C1:C23 and B53:B75 to C24:C46)
Then in the case of A125, instead of copying B100:B150 to C52:C102, I need to copy B100:B122 to C52:C74 and B128:B150 to C74:C102.

Initially I was worried I wouldn't be able to explain this, but hope this is not too complicated

[Bob Phillips]

And what gets copied if A10 is not zero?

[Ugur]

That will never be the case in my data set. the first 25 and the last 25 cells in A are all 0's

[Bob Phillips]

Sub ProcessData()
Dim LastRow As Long
Dim NextRow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
For i = 26 To LastRow

If .Cells(i, "A").Value <> 0 Then

.Cells(i - 25, "B").Resize(23).Copy .Cells(NextRow, "C")
NextRow = NextRow + 23
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Ugur]

This script is copying only 25 cells above, but it didnt copy below 25cells to the C

Am I right ?

[Bob Phillips]

Oh, I see. I didn't get that first time.

is this better?



Sub ProcessData()
Dim LastRow As Long
Dim NextRow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
NextRow = 1
For i = 26 To LastRow

If .Cells(i, "A").Value <> 0 Then

.Cells(i - 25, "B").Resize(23).Copy .Cells(NextRow, "C")
.Cells(i + 3, "B").Resize(23).Copy .Cells(NextRow + 23, "C")
NextRow = NextRow + 46
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Ugur]

This works for every 26th cell :S, but what i want to do is to search column A to check if there's 1 in this column or not to proceed to the next step.

can we also please do it like this:

so lets say in column A, (A25), it found "1"..

let it miss B24,B25 and B26 (exclude B24, B25 and B26)

then copy 25 observations above B24 (excluding B24) - so starting from B23 and counting backwards 25 observations
and 25 observations below B26 (excluding B26) - so starting from B27 and counting forward 25 observations

to the column C .

[Bob Phillips]

There are not 25 observations above B23, it can be 24 at most.

You are changing your requirements now, in post #11 you said ... In fact, I don't really need to copy B25:B75, but more precisely, B25:B47 and B53:B75 ... which is 23 cells in each block (which is what I believe that my latest code does), now you are saying 25.

You can see my code, you should be able to modify it for what you want now, it is very simple code.

[Ugur]

You're right, no 25 observations above B23. That part is not hard to edit. But your code only looks every 25th cell to check if its 0 or 1. But I want to search the entire column. Maybe A88 has 1 ?

[Ugur]

Thanks for all your help, guys! I solved the problem.


______________________________________________________________
Axiri size haqqin yolun oyretdim...
-Anatoli Bonishevski