. find (VBA) not working

[dannyok90]

Hi All,

The code bellow is looking for values (Materials*) but its not finding en exact match, its finding any cell with Materials or any part of the word in it and copy and pasting from there :/

Any ideas?


Thanks,
Dan

Sub MaterialsInsertNewRow()
    ' Materials Controls
    Dim b As Object, cs As Integer, LR As Integer
    Dim rng As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    LR = Sheet1.Range("E:E").Find("Materials*", , xlValues, xlWhole, , xlNext).Row - 2
    Set b = ActiveSheet.Buttons(Application.Caller)
With b.TopLeftCell
Sheet1.Unprotect Password:="rse1"
    cs = .Row
    Sheet1.Range("E:E").Find("Materials*", , xlValues, xlWhole, , xlNext).Offset(-1, 0).EntireRow.Copy
    Rows(cs).Offset(.Rows.Count + 0).Insert
    Rows(cs).Offset(.Rows.Count + 0).Hidden = False
Sheet1.Protect Password:="rse1"
  End With
  
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
  
End Sub

 

[Norie]

If you only want to find the row in column E that has the value 'Materials' perhaps you could use Application.Match instead of Find.

Sub MaterialsInsertNewRow()
    ' Materials Controls
Dim b As Object, cs As Integer, LR As Integer
Dim rng As Range
Dim Res As Variant

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Res = Application.Match("Materials", Sheet1.Columns(5), 0)
    
    If Not IsError(Res) Then
        LR = Res - 2
    Else
        MsgBox "Materials row not found!", vbExclamation
        Exit Sub
    End If
    
    Set b = ActiveSheet.Buttons(Application.Caller)
    
    With b.TopLeftCell
        Sheet1.Unprotect Password:="rse1"
        cs = .Row
        Sheet1.Rows(LR - 1).Copy
        Rows(cs).Offset(.Rows.Count + 0).Insert
        Rows(cs).Offset(.Rows.Count + 0).Hidden = False
        Sheet1.Protect Password:="rse1"
    End With
  
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
  
End Sub

 

[dannyok90]

Hi Norie,
Thanks!
The code works however its still not finding the exact value, so:
I have a total row thats called Named materials in Column E (5) and above it in a hidden row is
the row thats copied and inserted hence the -2
above that are rows the rows that people add info to, if one of the cells have materials in as a value then
your code stops at that cell and copies the cel above that rather than looking down to the totals row and copying
the hidden row above it.
i tried adding an asterix to "materials*" and adding an asterixs coloured in white to the actual cell but no such look
is there a way to make the value its lookinf for explicit?
Thanks,
Dan

 

[Norie]

Dan

What exactly are you looking for?

Is it 'Materials' or 'Named Materials'?

 

[dannyok90]

Dan

What exactly are you looking for?

Is it 'Materials' or 'Named Materials'?

Hi Norie,

I’m looking for “materials*”

The asterix makes it unique because in the same column it could say materials in another row.

I need to find the materials with the asterix by it.

Hope that makes sense...

Thanks,
Dan

 

[Norie]

Dan

That could be the problem, * is used as a wildcard character to find partial matches, that's probably why you had a problem with Find and it's definitely why Application.Match didn't work properly.

 

[dannyok90]

Hi Norie

Thanks for your reply!
In the end I just moved the column that the I was looking for the match in, simple fix but works.. ill keep in mind the asterix being a wild card for future use.


Also, thanks for the bit of code, its a lot cleaner than using .find!


Cheers,
Dan

 

[steve the fish]

If you literally need to match or find 'materials*' then you could use eg:

=MATCH("materials~*",A1:A100,0)

or with find look for:

materials~*

and match entire cell contents.