reference change vba

[rs2k]

I have a line of code that uses a fixed criteria;

 If .Value Like "*DELETE*" Then .EntireRow.Delete

Is there a way to change "*DELETE*" to say a comboBox value or cell reference?

I've tried

 If .Value Like ComboBox.value Then .EntireRow.Delete

&

 If .Value Like .Range("A1").value Then .EntireRow.Delete

But no joy,

Cheers
Colin

 

[mvptomlinson]

Hi rs2k, don't put a period before the Range("A1").Value, since you're already using a With statement that points to another cell address, presumably.

If .Value Like Range("A1").value Then .EntireRow.Delete

If you're still hoping to use wildcards, then just add them before and after, like so:

If .Value Like "*" & Range("A1").Value & "*" Then .EntireRow.Delete

 

[rs2k]

Thanks for the reply, that has stopped the errors (3 days I've been playing with that)

I still have a problem with the full code though. I use the code for retrieving invoices that search for 'Not Invoiced' and it copies & returns what is expected (The 'Not Invoiced' has been hard coded as per first post) When I try and place a value in a cell for it to search on a different criteria (Name) the code doesn't work.

What I am trying to achieve is to find/search on a single name from a list and paste the row(s) of all occurances into another ws.

Here's what I have; original code that works;

Sub CopySignificant()
  'Copy cells of cols A,F,E,D from rows containing "Not Invoiced" in
  'col H of the active worksheet (source sheet) to cols
  'A,B,C,D,E,F of Sheet2 (destination sheet)
  Dim DestSheet        As Worksheet
  Set DestSheet = Worksheets("Clients to Invoice")
  
  Dim sRow       As Long     'row index on source worksheet
  Dim dRow       As Long     'row index on destination worksheet
  Dim sCount     As Long
  sCount = 0
  dRow = 1

  For sRow = 1 To Range("H65536").End(xlUp).Row
     'use pattern matching to find "Not Invoiced" anywhere in cell
     If Cells(sRow, "H") Like "Not Invoiced" Then
        sCount = sCount + 1
        dRow = dRow + 1
        'copy cols A,F,E & D
        Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
        Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
        Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
        Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "D")
        Cells(sRow, "E").Copy Destination:=DestSheet.Cells(dRow, "E")
        Cells(sRow, "F").Copy Destination:=DestSheet.Cells(dRow, "F")
        Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G")
     End If
  Next sRow
  
  MsgBox sCount & " Records for Invoicing", vbInformation, "Transfer Done"

End Sub

and the amended one that doesn't;

Private Sub cmdSingleInvoiceRetrieve_Click()

  'Copy cells of cols A,B,C,D,E,F,G from rows containing "K2" in
  'col A of the active worksheet (source sheet) to cols
  'A,B,C,D,E,F,G of Sheet2 (destination sheet)
  Dim DestSheet        As Worksheet
  Set DestSheet = Worksheets("Clients to Invoice")
  
  Dim sRow       As Long     'row index on source worksheet
  Dim dRow       As Long     'row index on destination worksheet
  Dim sCount     As Long
  sCount = 0
  dRow = 1
Sheets("Bookings").Range("K2").Value = ComboBox1.Value
  For sRow = 1 To Range("A65536").End(xlUp).Row
     'use pattern matching to find "K2" anywhere in cell
     If Cells(sRow, "A") Like Range("K2").Value Then
        sCount = sCount + 1
        dRow = dRow + 1
        'copy cols A,F,E & D
        Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
        Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
        Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
        Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "D")
        Cells(sRow, "E").Copy Destination:=DestSheet.Cells(dRow, "E")
        Cells(sRow, "F").Copy Destination:=DestSheet.Cells(dRow, "F")
        Cells(sRow, "G").Copy Destination:=DestSheet.Cells(dRow, "G")
     End If
  Next sRow
  
  MsgBox sCount & " Records for Invoicing", vbInformation, "Transfer Done"

End Sub

The 'DELETE' code is something else I'm trying with this bit of code, not that important at the moment, I just want to get it working, and more so, understand how it works, so I can tinker with it.

Thanks for the help

Cheers
Colin

 

[rs2k]

Still struggling with this, the Msg box is displaying 1 record found, but I know there is 3 records there. Also it is not copying across to the destination sheet. As I stepped through the code, this line;

Set DestSheet = Worksheets("Clients to Invoice")

states DestSheet = Nothing, is this correct or should it = Clients to Invoice?

Cheers
Colin

 

[rs2k]

Sorted, I changed;

Sheets("Bookings").Range("K2").Value = ComboBox1.Value

to this

Sheets("Bookings").Activate

And moved the ComboBox1 value to an earlier procedure.

Cheers
Colin