I don't know what I'm doing!

[Jon von der Heyden]

Hi,

I've written the following code:

Sub Simulate()

Application.Calculation = xlCalculationManual

Dim c As Range

For i = 1 To 675

    For Each c In Range("rng")
        If c.Value = Range("tgt").Value Then
            Rows(c).Copy

        End If
    Next c
    Calculate
    
Next

Application.Calculation = xlCalculationAutomatic

End Sub

Basically, all I want is to copy the row when c.value = tgt, but it doesn't like the syntax I'm trying. Once copied I want to move it to a sheet called "output" and paste to the next empty row (but I should be fine with this piece of code).

Cheers,
Jon

 

[Oorang]

You have your variables in quotes.

Sub Simulate()

Application.Calculation = xlCalculationManual

Dim c As Range

For i = 1 To 675

For Each c In Range("rng")
If c.Value = Range("tgt").Value Then
Rows(c).Copy

End If
Next c
Calculate

Next

Application.Calculation = xlCalculationAutomatic

End Sub

 

[Oaktree]

I'm not sure what the 1 to 675 is for.

And, you weren't pasting your data anywhere.

Try:

Sub Simulate()

Application.Calculation = xlCalculationManual

Dim c As Range

For Each c In Range("rng")
If c.Value = Range("tgt").Value Then
c.EntireRow.Copy Sheets("output").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next c
Calculate


Application.Calculation = xlCalculationAutomatic

End Sub

 

[Joe Was]

rng & tgt need to be Named Ranges on your sheet or you need to Set these ranges or define there location limits.

Do you have these Ranges Named Using: Toolbar: Insert - Name Define...?

 

[Jon von der Heyden]

Thanks all of you, got this solved now. I wasn't clear, but yes "tgt" and "rng" are defined range names (i.e. Insert->Names->Define).

I think that the problem was with the row selection syntax, works with EntireRow.Copy.

Thanks again...

 

[Joe Was]

Good glad Oaktree's catch helped.