Issue with VBA Find function

[Cdn_Medic]

Good day Excel peeps,

I have run into an issue trying to code a document for work.

I want to look for a string store in the variable "name". If that string is found, I want to add 1 to which ever number is in column I. If the string is not found, I want the values of column A, F and I to be respectively the variable name, nsn and qty1.

Here is the code I have:

Private Sub CommandButton5_Click()
Dim name As String
Dim nsn As String
Dim qty1 As Integer
Dim qty2 As Integer
Dim rgFound As Range


name = Sheets("RightLocker").Range("A1").value
nsn = Sheets("RightLocker").Range("C2").value
qty1 = 1


With Sheets("OrderForm").Range("A11:A45")
Set rgFound = .Find(What:=name)


If Not rgFound Is Nothing Then
Set outsh = Sheets("Orderform")
    outsh.Range("A15").value = "Test"
Else
Set outsh = Sheets("Orderform")
outrow = outsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    outsh.Cells(outrow, 1).value = name
    outsh.Cells(outrow, 6).value = nsn
    outsh.Cells(outrow, 9).value = qty1
End If
End With


End Sub

This part works perfectly:

Else
Set outsh = Sheets("Orderform")
outrow = outsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    outsh.Cells(outrow, 1).value = name
    outsh.Cells(outrow, 6).value = nsn
    outsh.Cells(outrow, 9).value = qty1

But I can't get this part to work:

If Not rgFound Is Nothing Then
Set outsh = Sheets("Orderform")
    outsh.Range("A15").value = "Test"
[/code ]

Any advice?

Thanks

 

[Fluff]

Is this what you mean

If Not rgFound Is Nothing Then
   Set outsh = Sheets("Orderform")
    outsh.Range("A15").Value = Name
    outsh.Range("F15").Value = nsn
    outsh.Range("I15").Value = Qty1
Else

 

[Cdn_Medic]

No, that "Test" value was only to test if the code worked.

But the find function doesn't work like I want. It won't display the "test" which means that it doesn't find the string stored in the name variable even though it is located in range A11.

 

[Fluff]

Are you look to do a complete or partial match?

 

[Cdn_Medic]

Complete.

To provide more background info, this code is used to generate an order form. I want to add to the quantity being ordered instead of creating a duplicate. Hence the Find function.

 

[Fluff]

Ok, how about

Set rgFound = .Find(Name, , , xlWhole, , , False, , False)

 

[Cdn_Medic]

So I gave that a try and it still doesn't work.

Here is the complete final code:

Private Sub CommandButton5_Click()
Dim name As String
Dim nsn As String
Dim qty1 As Integer
Dim qty2 As Integer
Dim rgFound As Range


name = Sheets("RightLocker").Range("A1").value
nsn = Sheets("RightLocker").Range("C2").value
qty1 = 1


With Sheets("OrderForm").Range("A11:A45")
'Should look for the item name in column A
Set rgFound = .Find(name, , , xlWhole, , , False, , False)


'If the name is not in anywhere in column A, should insert the variables
'name, nsn and qty1 in column A, F and I respectively


If rgFound Is Nothing Then
Set outsh = Sheets("Orderform")
outrow = outsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    outsh.Cells(outrow, 1).value = name
    outsh.Cells(outrow, 6).value = nsn
    outsh.Cells(outrow, 9).value = qty1


'If the name is found in column A, should take whatever quantity
'is in column I and add 1 to it
Else
Set outsh = Sheets("Orderform")
    qty2 = rgFound.Offset(0, 8).value
rgFound.Offset(0, 8).value = qty1 + qty2
End If
End With


End Sub

 

[DanteAmor]

I tried your last macro and it works for me.


I made some small changes.


If you only occupy once the range: Sheets("OrderForm").Range("A11:A45"), it makes no sense to put it in the With.
Instead, you set the sheet in the IF or in the ELSE, so you better set it before.

Private Sub CommandButton5_Click()
  Dim name As String, nsn As String, qty1 As Integer, rgFound As Range, outsh As Worksheet, outrow As Long
  
  name = Sheets("RightLocker").Range("A1").Value
  nsn = Sheets("RightLocker").Range("C2").Value
  qty1 = 1
[COLOR=#0000ff]  Set outsh = Sheets("Orderform")[/COLOR]
  [B][COLOR=#ff0000]Set rgFound = outsh.Range("A11:A45").Find(name, , xlValues, xlWhole)[/COLOR][/B]
  If rgFound Is Nothing Then
    'If the name is not in anywhere in column A, should insert the variables
    outrow = outsh.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    outsh.Cells(outrow, "A").Value = name
    outsh.Cells(outrow, "F").Value = nsn
    outsh.Cells(outrow, "I").Value = qty1
  Else
    'If the name is found in column A, should take whatever quantity
    rgFound.Offset(0, 8).Value = rgFound.Offset(0, 8).Value + qty1
  End If
End Sub

You can tell us what you have in the Sheets("RightLocker").Range("A1").Value, Is it a text or a date or is it a formula or does it have blank spaces before or after the data?


And also put what you have on the sheet: "Orderform"

 

[Cdn_Medic]

I found the issue. I wasn't aware that you need to include the complete range of merged cells.

I changed:

Set rgFound = outsh.Range("A11:A45").Find(name, , xlValues, xlWhole)

To:

Set rgFound = outsh.Range("A11:E45").Find(name, , xlValues, xlWhole)

And now it works perfectly.

Thank you to you both for you help. It at least helped me clean up my code.

 

[Fluff]

Glad we could help & thanks for the feedback.
I would however recommend getting rid of the merged cells. They play havoc with VBA & will only cause you problems in the future.
If they are merged across columns, use Center across selection instead https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/