"With" procedure not working....

[BHomestar]

Hey everyone,

I am not sure why my computer doesnt like the below procedure. I am trying to take information from sheets"PIP" and put it in the column format of sheets"Loans". When I run this no errors are detected but the program freezes. All of my other macros seem to work fine but this one. Is there a better way to write this?


Sub columnfix()

Dim L As Integer

Sheets("Loans").Select

With Sheets("PIP")

For L = 3 To 20

.Range("A3").Value = Range("A" & L).Value
.Range("B3").Value = Range("I" & L).Value
.Range("C3").Value = Range("J" & L).Value
.Range("D3").Value = Range("X" & L).Value
.Range("E3").Value = Range("AC" & L).Value

Next

End With

End Sub


Any help is much appreciated!!

Thanks everyone,

Bryan

 

[TommyGun]

What do you mean "program freezes"? Also, why are you overwriting the same data 18 times?

And to answer your question...yes there are better ways to write this, but without knowing what you are actually trying to accomplish, it would be hard to help.

 

[BHomestar]

TommyGun,

What I am trying to do is take the info in columns A, I, J, X & AC from the PIP sheet and put it in columns, A, B, C, D & E on the Loans sheet. I was trying to do this as loop process for rows 3 through 20.

I hope this makes it more clear.

Thanks for your help.

Bryan

 

[TommyGun]

This should get you a start in the right direction...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MoveData()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> 20
        <SPAN style="color:#00007F">With</SPAN> Sheets("Loans")
            .Cells(i - 2, 1) = Sheets("PIP").Cells(i, 1).Value
            .Cells(i - 2, 2) = Sheets("PIP").Cells(i, 9).Value
            .Cells(i - 2, 3) = Sheets("PIP").Cells(i, 10).Value
            .Cells(i - 2, 4) = Sheets("PIP").Cells(i, 24).Value
            .Cells(i - 2, 5) = Sheets("PIP").Cells(i, 29).Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[BHomestar]

Thank you TommyGun!!

Your work is excellant.

 

[Legacy 21301]

Why not simplify it to :-

Sub MoveData()
With Sheets("Loans")
.[A3:A20] = Sheets("PIP").[A3:A20].Value
.[B3:C20] = Sheets("PIP").[I3:J20].Value
.[D3:D20] = Sheets("PIP").[X3:X20].Value
.[E3:E20] = Sheets("PIP").[AC3:AC20].Value
End With
End Sub

 

[BHomestar]

Thank you for the response Ponsy Nob!