What am I missing?

[Metrazal]

Ok,

With the following code, I want to copy columns J,K,L, & M from
"SJournal" if the cell in column "A" of "Sjournal" matches the cell in
column "B" of "Invoice". I am running this as a module since
"Sjournal" and "Invoice" are actually two seperate sheets. What am I
doing wrong or am I just really messed up with my concept?


For t = 1 To 9999
If Worksheets("SJournal").Cells(t, "A") =
Worksheets("Invoice").Cells(t, "B") Then Worksheets("Invoice").Cells(t,
"J") = Worksheets("SJournal").Cells(t, "J")
If Worksheets("SJournal").Cells(t, "A") =
Worksheets("Invoice").Cells(t, "B") Then Worksheets("Invoice").Cells(t,
"K") = Worksheets("SJournal").Cells(t, "K")
If Worksheets("SJournal").Cells(t, "A") =
Worksheets("Invoice").Cells(t, "B") Then Worksheets("Invoice").Cells(t,
"L") = Worksheets("SJournal").Cells(t, "L")
If Worksheets("SJournal").Cells(t, "A") =
Worksheets("Invoice").Cells(t, "B") Then Worksheets("Invoice").Cells(t,
"M") = Worksheets("SJournal").Cells(t, "M")
Next t


Thanks for any help in advance,

Met

 

[Tim Barlow]

Met,

That code should work, though it could be tidied up with just one 'if'
statement:

Dim t As Long
For t = 1 To 9999
If Worksheets("SJournal").Cells(t, "A") = Worksheets("Invoice").Cells(t,
"B") Then
Worksheets("Invoice").Cells(t, "J") =
Worksheets("SJournal").Cells(t, "J")
Worksheets("Invoice").Cells(t, "K") =
Worksheets("SJournal").Cells(t, "K")
Worksheets("Invoice").Cells(t, "L") =
Worksheets("SJournal").Cells(t, "L")
Worksheets("Invoice").Cells(t, "M") =
Worksheets("SJournal").Cells(t, "M")
End If
Next t

However, doing 9999 rows, it will take some time to run (when you test the
code, start with a smaller number). It would be faster to read the data in
two arrays, modify the arrays then paste them back with something like:

Dim t As Long
Dim jrnlArr As Variant
Dim invArr As Variant

jrnlArr = Sheets("SJournal").Range("A1:M9999")
invArr = Sheets("Invoice").Range("A1:M9999")

For t = 1 To 9999
If jrnlArr(t, 1) = invArr(t, 2) Then
invArr(t, 10) = jrnlArr(t, 10)
invArr(t, 11) = jrnlArr(t, 11)
invArr(t, 12) = jrnlArr(t, 12)
invArr(t, 13) = jrnlArr(t, 13)
End If
Next t
Sheets("Invoice").Range("A1:M9999") = invArr


although this will only copy the cell contents across - no formatting will
be copied.

HTH

Tim