Working with two worksheets

[jrnyman]

I'm trying to look at a cell on worksheet 2, and based on whether it is empty, matches a cell in worksheet 1, or other do some stuff. I can't figure out how to work the references between the sheets. Any advice?


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

Sheets("Data").Select
If JDataRow.Value = "Average" Then
If ASpreadsRow.Value = "" Then
Sheets("Data").Range("B" & DataRow).Select
Selection.Copy
Sheets("Spreads").Select
Range("A" & SpreadsRow).Select
ActiveSheet.Paste
End If
If ASpreadsRow = BDataRow Then
If ADataRow.Value = "L" Then
Sheets("Data").Select
Range("K" & DataRow).Select
Selection.Copy
Sheets("Spreads").Select
Range("C" & SpreadsRow).Select
ActiveSheet.Paste
End If
If ADataRow.Value = "B" Then
Sheets("Data").Select
Range("K" & DataRow).Select
Selection.Copy
Sheets("Spreads").Select
Range("B" & SpreadsRow).Select
ActiveSheet.Paste
End If
SpreadsRow = SpreadsRow + 1
End If
End If
Next DataRow

End Sub

 

[Russell Hauf]

What exactly can't you figure out? What is going wrong?

 

[jrnyman]

At the line
'Set ADataRow = Sheets("Data").Range("A" & DataRow)
I get a run-time error '9'
Subscript out of range error.

Also, is there a difference between these two sets of commands?:

(1) Sheets("Sheetname").Range("A1").Select
(2) Sheets("Sheetname").Select
Range("A1").Select

 

[Dave Hawley]

Hi

Not sure it is you are doing but it honestly looks like you going about in the hard way. Consider inserting a formula in the needed range (via VBA) that will do the comparison. Then flag the 'non-matching' cells with any old number and other with any old text. Then use the SpecialCells method to work with your datain one go. I would say you could speed up things about 100 times and cut down on your lines of code by 90%


In regards to your errors:
'Set ADataRow = Sheets("Data").Range("A" & DataRow)

I would hazard a guess that 1: There is no sheet called "Data" 2: "DataRow" has value of Zero or is the wrong variable type. 3: "DataRow" has a value greater than 65536.

While there is no difference in the cell referenced in your 2 examples # 1 Would fail if the sheet was not active while number 2 wouldn't. Best way for this sort of thing (if you really must select) is:

Application.Goto Sheets("Sheetname").Range("A1"), True