Conditional data move syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 said:
I'm not sure what I doing wrong but my posts don't seem to be received.
Click to expand...
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
Click to expand...
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.
 
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
 
Back
Top