MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Another problem with coding

  Asked By: Ayden    Date: Feb 20    Category: MS Office    Views: 997

Hi guys,could anyone kindly help me with this? i am trying to compare
2 columns in 2 different worksheets, if they are equal, it will copy
a value from another location to the other spreadsheets..However,
when i run the program it gives an error "subscript out of range"
at the statement "If .Value = Worksheets("BU").Cells(i, 1).Value Then"

could anyone help me look at the code and improve on it?

Dim i As Integer
Dim J As Integer

For i = 2 To 10
For J = 5 To 2000
With Worksheets("raw data").Cells(J, 2)
Worksheets("BU").Cells(i, 2).Select
Worksheets("raw data").Cells(J, 1).Select

End If

End With

Next J

Next i

End Sub



4 Answers Found

Answer #1    Answered By: Jake Evans     Answered On: Feb 20

Do the worksheets  "BU" and "raw data" exist in the workbook?

Answer #2    Answered By: Virgil Foster     Answered On: Feb 20

Based on what your code  is doing, you don't need to use VBA. Put the following
formula in cell A5 on the "raw data" sheet and copy it down through row 2000:


If the value in column B is found in the range on the BU sheet, that value
will be returned in column 1 by the Vlookup function. If not found, #N/A is
returned. Convert A5:A2000 to values, then Replace all #N/A with nothing.

Answer #3    Answered By: Penny Clark     Answered On: Feb 20

Most probably, the variable i from Cells(i, 1) was undefined.

Answer #4    Answered By: Mohammed Evans     Answered On: Feb 20

I sent a non-VBA solution for this already, but if you need to do it in VBA (as
part of a larger procedure or system), the following code  should work:

Sub CompareSheets()
Dim x As Long, retval
On Error Resume Next
Sheets("raw data").Activate
For x& = 5 To 2000
retval = Application.WorksheetFunction.VLookup(ActiveSheet.Cells(x&,
2).Value, Sheets("BU").Range("A2:A10"), 1, False)
If retval = ActiveSheet.Cells(x&, 2).Value Then
ActiveSheet.Cells(x&, 1).Value = retval
End If
Next x&
End Sub

Didn't find what you were looking for? Find more on Another problem with coding Or get search suggestion and latest updates.