VBA If function help

[kdm]

Hello,

I need to use an If function to search for a text sring in one column and then copy the results from another column on to another worksheet.

For example:

1 A
2 B
3 C
4 D

I need to search in the first column for numbers > 2 and then copy the true results from the second column. In this exmaple, B, C and D would be copied.

I am a beginner, so this may be easier than I think, nonetheless I am still struggling and can't find much help online!

Any help would be much appreciated.

Thanks,

kdm

 

[VoG]

Try something like this

Sub Cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("A" & i)
            If .Value > 1 Then .Offset(, 1).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End With
End Sub

 

[Domski]

As an example:

Sub Test()
 
With Sheets("Sheet1").Range("A1").CurrentRegion
 
    .AutoFilter Field:=1, Criteria1:=">=2"
    .SpecialCells(xlVisible).Copy Sheets("Sheet2").Range("A1")
    .AutoFilter
 
End With
 
End Sub

Copying from Sheet1 to Sheet2, assuming you have a header row in Row 1.

Dom

 

[kdm]

This is really helpful, cheers Peter and Dom.

 

[kdm]

Trying to run before I can walk...

I'm now getting stuck on idetifying the column to search. In further detail, I need to search in column A for any text strings containing "red". Peter, your example works for the copy and paste bit (thank you), but the condition is slightly different (due to my initial explanation).

Here is what I have:

If .Column("C") = Worksheet("Pilot").Range("C5").Value Then
.Offset(, 4).CopyDestination = Sheets("Pilot").Range("D25" & Rows.Count).End (x1Up).Offset(1)

I need to do this many times, hence the reference to a specific cell, I will later change this to a relative Offset description. I am particularly confused with the bit in red as I can't seem to figure out how I identify a specific column to run the If condition in.

As an example:

blue red 1
blue yellow 2
white red 4

What I am trying to get to is that the macro would search the first column for rows containing "red" and then copy the corresponding value in the 2nd column. So in this example it would return 1 and 4.

Sorry to be a pain guys, any help (again) would be very much appreciated.

Thanks for your help so far.

kdm

 

[Domski]

You need to loop through column C like this testing each cell:

Sub test()
 
Dim rngLoopRange As Range
 
For Each rngLoopRange In Range("C:C")
 
If rngLoopRange = Worksheet("Pilot").Range("C5").Value Then
 
    rngLoopRange.Offset(, 4).Copy Destination = Sheets("Pilot").Range("D25" & Rows.Count).End(x1Up).Offset(1)
 
End If
 
Next rngLoopRange
 
End Sub

Dom

 

[kdm]

Dom,

I'm running into some additional issues with this code. Here is what I have:

For Each rngLoopRange In Application.Workbooks("Raw Data.xls").Worksheet("Pipeline").Range("C:C")
If rngLoopRange = Worksheets("IM").Range("C5").Value Then
rngLoopRange.Offset(, 4).Copy Destination = Application.Workbooks("Pilot.xls").Worksheets("IM").Range("D25" & Rows.Count).End(x1Up).Offset(1)

Once I run this I get an error saying 'Object doesn't support this property or method' and the debugger highlights the red area.

There isn't any information in my book or online (that I can find) on rngLoopRange.

Once again any help is very much appreciated.

Thanks,

kdm

 

[Domski]

There were quite a few small but significant errors in your code:

Dim rngLoopRange As Range

For Each rngLoopRange In Workbooks("Raw Data.xls").Worksheets("Pipeline").Range("C:C")
If rngLoopRange = Worksheets("IM").Range("C5").Value Then
rngLoopRange.Offset(0, 4).Copy Destination:=Workbooks("Pilot.xls").Worksheets("IM").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
End If

Next rngLoopRange

Not tested it but think that should be better.

You won't find rngLoopRange in the help as it's just a variable name that I have declared. I could just as easily have called it Bob.

Dom

 

[kdm]

Dom,

I've made those changes. Thanks.

Another error is now coming up, it is:

Dim rngLoopRange As Range
    Application.ScreenUpdating = False
    For Each rngLoopRange In Workbooks("Raw Data.xls").Worksheets("Pipeline").Range("C:C")
    [COLOR=red]If rngLoopRange = Workbooks("Pilot").Worksheets("IM").Range("C10").Value Then[/COLOR]
        rngLoopRange.Offset(0, 4).Copy Destination:=Workbooks("Pilot.xls").Worksheets("IM").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
End If

The error is 'subscript out of range', the titles are correct and the cell has data in it, why isn't it running properly? The red area is highlighted by the debugger.

Thanks again.

kdm

 

[Domski]

Is the Pilot workbook open?

Dom