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)

 

[Norie]

I actually meant Selecting is rarely needed in VBA.

Can you post the code you are using that copy/pastes?

As for the problem with Find, try using False for SearchFormat instead of True and/or changing LookIn to xlFormulas.

 

[ZikO]

Hi Norie,
To be honest, I don't have any code for Copy/Paste. I have done it manually. I couldn't spend more time on the code. The main cumbersome part included selection of the required fields. Copy / Paste was an easy part once proper data were highlighted. I'll try to change parameters in Find and see if it works.

Cheers!