VBA Combining two macros (Find Next and Auto Caps)

[clprdctn]

Hello everyone,

I'm trying to combine the below two macros into one.
Basically the Find next macro is a box i created in the spreadsheet to find the word within the spreadsheet (To avoid 'Ctrl + F' all the time).

They are both completely different macros but for the same sheet.

Thank you!


Find Next Macro

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Address <> "$C$1" Then Exit Sub
Set rng = Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlPart)
If rng Is Nothing Then GoTo NotFound
If rng.Address = Target.Address Then GoTo NotFound
rng.Activate
Exit Sub
NotFound:
MsgBox "Not found!", vbOKOnly, "Search for '" & Target.Value & "'"
End Sub



Auto Caps Macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B14:AJ1000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

[xladept]

Maybe:

Please Login or Register  to view this content.

[clprdctn]

I'm getting the following Error when I try to input anything in the Search box: "Run-time error '91' Object variable or With block variable not set"

And

I'm getting the following Error when I try to put any letters within the range mentioned above to see if it'll auto caps: "Run-time error '13': Type mismatch".

Thank you,

[xladept]

Wow - I only copied your code - can you post a sample that fails??

[clprdctn]

TEST1.xlsm

Yes for sure, please see the attachment.

Thanks.

[xladept]

It seems to be working now:

Please Login or Register  to view this content.

[clprdctn]

Thank you so much! It worked!

[xladept]

You're welcome! But, I thought of an improvement - switch the End If:Exit Sub

Please Login or Register  to view this content.

[clprdctn]

One last thing,

Now every time I delete any data (like a name or the blank search box) I get a Run-Time error 91: Object Variable or with block variable not set and it leads me to this If Intersect(Target, Range("I14:AJ1000")) <> "" Then

Can you please help me out with this?

Thanks.

[xladept]

Try this:

Please Login or Register  to view this content.

[clprdctn]

Thank you Xladept. The search box is fixed, it's not giving errors and the only thing left is the Name column (Column A), when i try to delete a name it's still giving that error.

[clprdctn]

Attached is the updated spreadsheet where I'm having issue.

If Target.Row>13 Then | Fixed the issue for the search box

If only there was something similar for the column, so it can capture everything other than column A.

[xladept]

I changed the Intersect verbiage and it works now??

Please Login or Register  to view this content.

[clprdctn]

Can't seem to get it to work so I moved around some cells and the way I display, it seems to be fine now. However I don't need the range to be uppercase now, I only need from A15:A1000 to be uppercase, rest i gave up on and with the changes i made - i do not need them anymore.

For that change I tried to change your code like this but it didn't work, it's giving errors:

If Not Intersect(Range("A15:A1000"), Target) Is Nothing Then
Target = UCase(Target): End If: Exit Sub

[xladept]

Weird - it works all the time for me now?

Can you post the new sample??

[clprdctn]

Test3.xlsm

Yes for sure, here is the attachment, i cut down some of it because it exceeds the allowable size. Since everything other than column A is numbers, the uppercase should only be for column A.

Thank you again.

[xladept]

Here's the new code:

Please Login or Register  to view this content.

[clprdctn]

Dude, you are seriously amazing! Thank you so much!

[xladept]

Your welcome!