Conditional data move syntax
 

I'm not sure what I doing wrong but my posts don't seem to be received. Anyway if this message is posted, I am having trouble with this VBA Macro. The If/Then Statement appears to be formatted incorrectly as the Macro always errors on the "Then .Cells(a, "K").......half of the statement.

I am trying to copy the data from one column "K" to the "J" column if the data in K is non zero. I do not know how many rows will be in the spreadsheet. The top row contains titles, hence stop at row 2. I will also need to Bold the copied data.


Dim a As Long
With Worksheets("OUTSTANDINGSALESORDERS")
For a = .UsedRange.Rows.Count To 2 Step -1
If .Cells(a, "K").Value 0 Then .Cells(a, "K").copy .Offset(0, -1)
Next
End With

Any help is appreciated

Thanks

Jon45

Conditional data move syntax
 

Try:

Dim a As Long
With Worksheets("OUTSTANDINGSALESORDERS")
For a = .UsedRange.Rows.Count To 2 Step -1
If .Cells(a, "K").Value 0 Then _
.Cells(a, "K").Copy .Cells(a, "K").Offset(0, -1)
Next
End With

or

Dim a As Long
With Worksheets("OUTSTANDINGSALESORDERS")
For a = .UsedRange.Rows.Count To 2 Step -1
With .Cells(a, "K")
If .Value 0 Then _
.Copy .Offset(0, -1)
End With
Next
End With


Regards

Trevor


"Jon45" wrote in message
...
I'm not sure what I doing wrong but my posts don't seem to be received.
Anyway if this message is posted, I am having trouble with this VBA Macro.
The If/Then Statement appears to be formatted incorrectly as the Macro
always errors on the "Then .Cells(a, "K").......half of the statement.

I am trying to copy the data from one column "K" to the "J" column if the
data in K is non zero. I do not know how many rows will be in the
spreadsheet. The top row contains titles, hence stop at row 2. I will
also need to Bold the copied data.


Dim a As Long
With Worksheets("OUTSTANDINGSALESORDERS")
For a = .UsedRange.Rows.Count To 2 Step -1
If .Cells(a, "K").Value 0 Then .Cells(a, "K").copy .Offset(0, -1)
Next
End With

Any help is appreciated

Thanks

Jon45

Conditional data move syntax
 

Thanks for your help Trevor,

The first example worked great. I just started VBA programming and the syntax is difficult for me.

Thanks again

Jon45

Conditional data move syntax
 

Glad to be able to help


"Jon45" wrote in message
...
Thanks for your help Trevor,

The first example worked great. I just started VBA programming and the
syntax is difficult for me.

Thanks again

Jon45