reference change vba

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
I have a line of code that uses a fixed criteria;

Code:
 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
Code:
 If .Value Like ComboBox.value Then .EntireRow.Delete
&
Code:
 If .Value Like .Range("A1").value Then .EntireRow.Delete

But no joy,

Cheers
Colin
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
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.
Code:
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:
Code:
If .Value Like "*" & Range("A1").Value & "*" Then .EntireRow.Delete
 
Last edited:

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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;
Code:
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;
Code:
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

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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;
Code:
Set DestSheet = Worksheets("Clients to Invoice")

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

Cheers
Colin
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Sorted, I changed;
Code:
Sheets("Bookings").Range("K2").Value = ComboBox1.Value

to this

Code:
Sheets("Bookings").Activate

And moved the ComboBox1 value to an earlier procedure.

Cheers
Colin
 

Watch MrExcel Video

Forum statistics

Threads
1,127,602
Messages
5,625,750
Members
416,133
Latest member
ToseSenpai

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