Problem with range object

[ZikO]

Hello,

First of all, I'd like to say I am totally new to Visual Basic for Application. I have some experiences with other languages: good with C++ and Java, moderate with C#, and none with VBA ;p. But today, I decided to give it a go when I started repeating the same actions in Excell 2007 from one sheet to another; I know I will have to be doing them again later.

My goal is IMO of a moderate level. I tried to do something today but I gave up. Where do you think I can start learning VBA?

The problem:
I need to work on block of cells whose a column range is constant = 6 whereas a ange of rows differs from one sheet to another. I need to select a number of non-adjacent rows (entirely) depending on criteria. I need to check criteria in three different column ranges; the selection is done for an entire row if one of three columns given a particular row matches a condition. I stuck at the Range object. I though I could make 3 different range objects for corresponding three columns and then make a Union but it looks like Union does not work with Range objects that does not contain anything: "Nothing". Perhaps someone can suggest something here. Many thanks if you have reached this line

Private Sub complex_filter()
    Dim rng_c1 As Range
    Dim rng_c2 As Range
    Dim rng_c3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim cel3 As Range
    Dim cel As Range
    Dim allMatches As Range
    Dim x As Integer
    Dim xBool(0 To 2) As Boolean
    Dim strNames(0 To 2) As String


    Set rng_c1 = ActiveSheet.Range(Range("B4"), Range("B4").End(xlDown))
    Set rng_c2 = ActiveSheet.Range(Range("C4"), Range("C4").End(xlDown))
    Set rng_c3 = ActiveSheet.Range(Range("D4"), Range("D4").End(xlDown))


    ' Left Column
    Set cel1 = FindAll("CALC", ActiveSheet, rng_c1)
    


    ' Middle column
    strNames(0) = "love"
    strNames(1) = "spotify"
    strNames(2) = "tv"
  
    ' Goeff's rent
    Set cel3 = rng_c3.Find(What:=-£450, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    ' Union
    Set allMatches = Union(cel1, cel2, cel3)

 

[wigi]

Where do we find cel2 in your code?

 

[ZikO]

Because it was not working, then I tried to test it and removed some lines. Below is the full code. Compiler indicates run time error in the line "Set

cel2 = Union(cel2, cel)": "Invalid procedure call or argument". I've read both objects must contain something but it is common that the find method may not return anything else but Nothing. I don't know how to overcome this problem.
​

Private Sub complex_filter()

    Dim rng_c1 As Range
    Dim rng_c2 As Range
    Dim rng_c3 As Range
    Dim cel1 As Range
    Dim cel2 As Range
    Dim cel3 As Range
    Dim cel As Range
    Dim allMatches As Range
    Dim x As Integer
    Dim xBool(0 To 2) As Boolean
    Dim strNames(0 To 2) As String


    Set rng_c1 = ActiveSheet.Range(Range("B4"), Range("B4").End(xlDown))
    Set rng_c2 = ActiveSheet.Range(Range("C4"), Range("C4").End(xlDown))
    Set rng_c3 = ActiveSheet.Range(Range("D4"), Range("D4").End(xlDown))


    ' Left Column
    Set cel1 = FindAll("D/D", ActiveSheet, rng_c1)
    


    ' Middle column
    strNames(0) = "love"
    strNames(1) = "spotify"
    strNames(2) = "tv"
    For x = 0 To 2
        Set cel = rng_c2.Find(What:=strNames(x), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not cel Is Nothing Then
            If x = 1 Then
                 Set cel2 = cel
            Else
                 Set cel2 = Union(cel2, cel)
            End If
        End If
    Next x
  
    ' Sum to find
    Set cel3 = rng_c3.Find(What:=-450, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    ' Union of all 3 Range objects
    Set allMatches = Union(cel1, cel2, cel3)


End Sub

This is the function I have prepared for FindAll:

Private Function FindAll(ByRef sText As String, ByRef oSht As Worksheet, ByRef sRange As Range) As Range    ' FindAll - To find all instances of the1 given string and return the row numbers.
    ' If there are not any matches the function will return false


    Dim cel, rFnd, myMatches As Range
    Dim celAddress As String 'Store address as text


    oSht.Activate
    With sRange
        Set cel = .Find(What:=sText, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not cel Is Nothing Then
            celAddress = cel.Address
            Set myMatches = cel
            Do Until cel Is Nothing
                Set cel = .FindNext(cel)
                If cel Is Nothing Or cel.Address = celAddress Then Exit Do
                Set myMatches = Union(myMatches, cel)
            Loop
            Set FindAll = myMatches
        Else
            Set FindAll = Nothing
        End If
    End With


End Function

 

[wigi]

Try:

For x = 0 To 2
        Set cel = rng_c2.Find(What:=strNames(x), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not cel Is Nothing Then
            If Not cel2 Is Nothing Then
                 Set cel2 = Union(cel2, cel)
            Else
                 Set cel2 = cel
            End If
        End If
Next

 

[ZikO]

Hi Wigi,

It is working now. Thanks for this change. I have an additional problem though. The method Find may be used to look for text or numeric value. If I am not mistaken and regardless the type of cell value, I seem to provide the first argument as text: .Find(What:="aaa", ...). What's the difference between numeric and text values of a cell? My cell value is for instance -450 and I have formatted it as -£450. How should I use .Find() to look for -450. I tried: .Find(What:="-£450", ...), .Find(What:=-450, ...) and It does not work. Thanks

 

[ZikO]

Hi Wigi,

It's me again. I could not get it worked with Currency formatting. For some reason in VBA the format returned by rng_c3.NumberFormat was this: "$#,##0.00_);[Red]($#,##0.00)" and I am using pound :/ : for instance -£180. I had to change formatting to number: "#,##0.00_);[Red](-#,##0.00)" and it works now. If you have any suggestion on that I will be very grateful.

Best regards

 

[Norie]

Try "-450".

That worked for me but I'll admit I thought it should be -450.

Actually I just tried -450 again and it worked.

 

[ZikO]

Try "-450".

That worked for me but I'll admit I thought it should be -450.

Actually I just tried -450 again and it worked.

Hi Norie,
Thanks for the answer. Unfortunately, no, it is not working if I put "-450". I have to change columns to Number formatting and then I can look for "-450" and it gets found but it is not working with Currency formatting.

I have also another problem. Now when I have all cells in a Range object, I would like to select entire rows (or rows between column 1 to let's say 5) for given cells. I tried a simple code:

'Select Entire rows
For Each cel In allMatches
    cel.EntireRow.Select
Next

but it only selects one row for the last cell in Range. Is there any method that allows me to add to selection? I tried Help but I am afraid I am too newbie to find anything. I also tried to Google the problem but I did not manage to find anything as well.

 

[Norie]

When I tested I also had the column formatted as Currency

Can you post the code that didn't working?

As for the selecting, which is very rarely if ever needed, try this to select the entire row.

allMatches.EntireRow.Select

To only select columns 1 to 5:

Intersect(allMatches.EntireRow, Columns(1).Resize(,5)).Select

 

[ZikO]

Can you post the code that didn't working?

At the moment I am trying the code below and it does not work for me when I use Currency formatting:

' Sum to find
formatNum = rng_c3.NumberFormat ' String variable
Application.FindFormat.NumberFormat = formatNum
Set cel3 = rng_c3.Find(What:="-450", LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)

As for the selecting, which is very rarely if ever needed, try this to select the entire row.

I can admit that what I am trying to do with the selection can be done in VBA. I simply need to select these rows and copy / paste them to another workbook. Because I have plenty of sheets, I need to run this macro for each sheet and append selected cells in the next sheet in another workbook.

Intersect(allMatches.EntireRow, Columns(1).Resize(,5)).Select

Thanks for the code. It works as required

Best regards