1
Sub rs()
    Dim i As Integer
    Dim randomVar As Variant
    Dim ra As Variant

    'Set ra = New Range
    For i = 4 To 30
        If IsEmpty(Range("B" & i).Value) = True Then GoTo line1

        Range("B" & i).Select
        randomVar = ActiveCell.Value 'stores name of project to be found in another worksheet

        Sheets("Sheet2").Select
        Set ra = Cells.Find(randomVar) 'finds project name

        If ra Is Nothing Then GoTo line1 'if not found continue for loop

        ra.Offset(columnOffset:=4).Copy  'copies found project's cost that is 4 colums right to the project name

        Sheets("Sheet1").Select
        Range("F" & i).Select
        ActiveSheet.Paste 'pastes the cost of the project from other sheet
line1:
    Next i
End Sub

Overview of what the code does:
The above code copies project name from sheet1 and looks for it in sheet2. If the project is found it copies the cost of the project that is 4 columns after the project name, and pastes that cost in sheet1 next to its respective project.

Problem:
It correctly places first few values in place in sheet1 but after that it crashes. Gives no error just picks wrong values from sheet2 and places in sheet 1. What could possibly be happening?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
tia
  • 137
  • 1
  • 2
  • 9
  • 1
    So is it crashing (Excel stops working) or just not doing what you want it to do? Those are completely different things. – Comintern Oct 18 '18 at 18:31

1 Answers1

1

You have a bunch of .Select calls, which can lead to confusion, as well as the infamous GoTo calls.

Instead, rework it to avoid these. Note: Change sheet names as necessary.

Sub rs_two()
Dim i As Long
Dim randomVar As String
Dim ra As Range

For i = 4 To 30
    If Not IsEmpty(Sheets("Sheet1").Range("B" & i).Value) Then
        randomVar = Sheets("Sheet1").Range("B" & i).Value
        Set ra = Sheets("Sheet2").Cells.Find(randomVar)
        If Not ra Is Nothing Then
            ra.Offset(0, 4).Copy Sheets("Sheet1").Range("F" & i)
        End If
    End If
Next i

End Sub
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • @Prarabdha - Coincidentally (or not), this is also a great opportunity for you to review [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/). Look at your code, and see if you can follow the logic in how I parsed it down to work directly with the data. A bonus is you can see how to re-work loops to avoid using `GoTo` calls. – BruceWayne Oct 18 '18 at 19:31
  • Thanks, I'll look at it – tia Oct 18 '18 at 19:48