syntax for ranges in macro

[jrnyman]

Can anyone help me with the syntax here?

Sub Extract()
SpreadsRow = 2
For DataRow = 2 To LastRow 'defined earlier
Set ADataRow = Sheets("Data").Range("J" & DataRow)
Set BDataRow = Sheets("Data").Range("J" & DataRow)
Set JDataRow = Sheets("Data").Range("J" & DataRow)
Set ASpreadsRow = Sheets("Spreads").Range("A" & SpreadsRow)
Set BSpreadsRow = Sheets("Spreads").Range("C" & SpreadsRow)
Set CSpreadsRow = Sheets("Spreads").Range("C" & SpreadsRow)
Set KDataRow = Sheets("Data").Range("J" & DataRow)


If JDataRow.Value = "Average" Then
If ASpreadsRow.Value = "" Then
Range("BDataRow").Select
Selection.Copy
Range(ASpreadsRow).Select
Selection.Paste
End If
If ASpreadsRow = BDataRow Then
If ADataRow.Value = "L" Then
Range(KDataRow).Select
Selection.Copy
Range(CSpreadsRow).Select
Selection.Paste
End If
If ADataRow.Value = "B" Then
Range(KDataRow).Select
Selection.Copy
Range(BSpreadsRow).Select
Selection.Paste
End If
SpreadsRow = SpreadsRow + 1
End If
End If
Next DataRow

End Sub

 

[Mark O'Brien]

What's your problem?

I'm assuming from your variable names that there might be a problem with these lines of code:

<pre>
Set ADataRow = Sheets("Data").Range("J" & DataRow)
Set BDataRow = Sheets("Data").Range("J" & DataRow)
Set JDataRow = Sheets("Data").Range("J" & DataRow)
Set ASpreadsRow = Sheets("Spreads").Range("A" & SpreadsRow)
Set BSpreadsRow = Sheets("Spreads").Range("C" & SpreadsRow)
Set CSpreadsRow = Sheets("Spreads").Range("C" & SpreadsRow)
Set KDataRow = Sheets("Data").Range("J" & DataRow) </pre>

If you were wanting to set "ADataRow", "BDataRow" etc to actually be in those columns (A, B etc) then you'd better changed the "J" in each of these to be "Range("A" & DataRow), etc...

 

[jrnyman]

Sorry, I realised i hadn't changed those after I posted.