Problem with range object

ZikO

New Member
Joined
Jul 14, 2012
Messages
11
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 :)

Code:
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)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Where do we find cel2 in your code?
 

ZikO

New Member
Joined
Jul 14, 2012
Messages
11
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.
Code:
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:
Code:
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Try:

Code:
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

New Member
Joined
Jul 14, 2012
Messages
11

ADVERTISEMENT

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

New Member
Joined
Jul 14, 2012
Messages
11
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 14, 2012
Messages
11
Norie said:
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:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
allMatches.EntireRow.Select

To only select columns 1 to 5:
Code:
Intersect(allMatches.EntireRow, Columns(1).Resize(,5)).Select
 

ZikO

New Member
Joined
Jul 14, 2012
Messages
11
Norie said:
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:
Code:
' 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)

Norie said:
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.
Code:
Intersect(allMatches.EntireRow, Columns(1).Resize(,5)).Select
Thanks for the code. It works as required :)

Best regards
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top