PDA

View Full Version : Transferring Data from 2 columns to another sheet using For Next loop



Doublemandal
12-02-2007, 11:50 AM
I am just a newbie cutting my teeth, so if the following appears simple, forgive me.
I have created the following Sub procedure, but I running into an application error 1024:


Sub ProcessKI()
Dim Count As Integer
For Count = 1 To 200
If Range("M8").Offset(Count - 1, 0) And Range("N8").Offset(Count - 1, 0) > 0 Then
Range("M8").Offset(Count - 1, 0).Select
Selection.Copy
Sheets("D").Select
Range("C3").Offset(Count - 1, 0).Select
ActiveSheet.Paste
Range("T8").Offset(Count - 1, 0).Select
Selection.Copy
Sheets("D").Select
Range("D3").Offset(Count - 1, 0).Select
Selection.Copy
End If
Next Count
End Sub

--------------------------

What I am trying to do, is to take a Date from the M column and N column and find out whether both have been filled out. If so, then I want to copy the Date from the M column and copy the difference in days between M and N found in column T. If either $M or $N or both have no date, then I do not want to copy either $M or $T. This is to continue down for 200 cells.
The reasoning, is that the information from $M is plotted on an X-axis versus $T plotted on the Y-axis on another sheet then transfer to a chart.
Any thoughts please?
I much appreciate any help.
Robert

Norie
12-02-2007, 11:58 AM
Robert

A few comments.

Don't use Count as a variable name - it's a property of many different things in VBA.

Your If statement doesn't look right - you don't actually appear to be comparing what's in column M to anything.

You don't need all that selecting.

Perhaps something like this untested code might work.

Sub ProcessKI()
Dim I As Long
Dim J As Long
For I = 1 To 200
If Range("M8").Offset(I - 1) > 0 And Range("N8").Offset(I - 1) > 0 Then
Range("M8").Offset(I - 1).Copy Sheets("D").Range("C3").Offset(J)
Range("T8").Offset(I - 1).Copy Sheets("D").Range("D3").Offset(J)
Selection.Copy
J = J + 1
End If
Next I

End Sub

Doublemandal
12-02-2007, 03:29 PM
Hello Norie, sorry about the crosspost, I didn't know about that.

I again appreciate your help very much. I put in the code in the worksheet and ran it.

It returned with this statement:

"Object doesn't support this property or method."

I am going to place it in the workbook and test it from there.

Regards,
Robert

Doublemandal
12-02-2007, 08:15 PM
Norie through some help, I was able to procure this code which suffices:



Sub ProcessKI()
Dim iCount As Integer
Dim iCount2 As Integer
For iCount = 1 To 200
If Worksheets("Procurement").Range("M8").Offset(iCount - 1, 0) > 0 _
And Worksheets("Procurement").Range("N8").Offset(iCount - 1, 0) > 0 Then
iCount2 = iCount2 + 1
Worksheets("Procurement").Range("M8").Offset(iCount - 1, 0).Copy
Worksheets("D").Range("C3").Offset(iCount2 - 1, 0).PasteSpecial Paste:=xlPasteValues
Worksheets("Procurement").Range("T8").Offset(iCount - 1, 0).Copy
Worksheets("D").Range("D3").Offset(iCount2 - 1, 0).PasteSpecial Paste:=xlPasteValues
End If
Next iCount
Application.CutCopyMode = False
End Sub

I am working on an automatic Private sub.

Norie
12-03-2007, 07:50 AM
Which is basically the same as the code I posted apart from the paste special. and a couple of other things:)