Thread: Cell move?

Hi,

I am prepping a spreadsheet using a macro that I have recorded. The macro does all general stuff like deleting columns and making specific words red.

There are two separate macro's as one piece of data is downloaded manually and the other is sent via email therefore, the deleted columns are in a different place etc. The spreadsheets appear different at the start but the end result will leave me with the data in exactly the same place for each version (downloaded/emailed)

I then run a second macro that makes all the red amounts in a certain column (D) a minus amount, finally I move the amounts that are black in the same column (D) 1 cell to the right and up 2.

The problem that I have encountered is when I perform the cell move macro on the downloaded data version.

It should act exactly the same as the email version but for some reason it fails telling me "Application-defined or object defined error"

The highlighted code it refers to is the ".Range("D" & i).Copy .Range("D" & i).Offset(-2, 1)" line

Code is below:

Sub MoveCells()
'
    Dim i As Long
    Dim LR As Long
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    With ws
        LR = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 2 To LR
            If .Range("D" & i).Font.ColorIndex = 1 Then
            .Range("D" & i).Copy .Range("D" & i).Offset(-2, 1)
            End If
        Next i
    End With
    End Sub

Any help would be greatly appreciated.

Thank you

hi jsho1
you could remove the offset

  .Range("D" & i).Copy Destination:=.Range("b" & i + 1)

oops

 .Range("D" & i).Copy Destination:=.Range("E" & i - 2)

you will get an error if the data is in the second row as two rows up is off the sheet

whats the code? or a sample workbook

try..

For i = 3 To LR

hmm. doesnt like the blank cells
so

 For i = 2 To LR
            If .Range("D" & i).Font.ColorIndex = 1 And .Range("D" & i).Value <> "" Then