Search and find row on other sheet without selecting it

[Shotokan]

Hi there...

I'm new to this forum so forgive me if I'm doing something wrong here...
First of all I learned a lot from reading through posts on this forum, so thanks to all you pro's who are so kind to share expertise and help.
My knowledge in VBA code is very limited.
I can usually understand what it does when reading it but writing is horrible for me.
I have a question about a piece of code that's bugging me so I hope someone can help me...

In short:

I have a user form "FormTest1" on sheet 1, the info from this form's textboxes need to be saved to the dataset on another sheet called "CLT". So I need the code to check column A and search for a row with a specific number that matches a textbox on the user form, let's say TextBox1. Once found it can overwrite the entire row. The code I posted does the perfectly fine, however because this codes actually selects the "CLT" sheet before writing to it the screen gets all choppy. Especially considering that I have like 8 sheets that have similar functionality, the code runs but it's not even a little smooth. It get's the job done though...

I know there has to be a better way for this, preferably by not selecting the sheets it needs to write to. If someone would be able to improve this code for me that would be great!

Just so you all know I did not write this, its just snippets of code collected left an right and copy pasted together

Here the actual code:


Sub TestSearchAndReplace()

Dim lastrow As Long
Dim ABnum As Double
Dim ABrng As Range
Dim WriteRow As Long
Dim Ws As Worksheet
Set Ws = Worksheets("CLT")

With Ws
Sheets("CLT").Select '''''''THIS ONE IS THE PROBLEM I THINK BUT THE CODE DOESN'T WORK WITHOUT IT
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ABrng = .Range("A1:A" & lastrow)
ABnum = FormTest1.TextBox1.Value
WriteRow = Application.Match(ABnum, ABrng, 0)
Cells(WriteRow, 1).Select
End With

Call ModuleUnprotectSheet.DisableSheetProtectionCLT

With ActiveCell
.Offset(0, 1).Value = EditClient_Form.TBFirstName.Value
End With


Call ModuleProtectSheet.EnableSheetProtectionCLT

End Sub


Any help would be much appreciated! Thanks in advance!

 

[PeteWright]

Hi @Shotokan

Could you upload your workbook or is there sensitive data on it?
If there is sensitive content, maybe you could replace it with some dummy data and upload it then.

Meanwhile I'll take a look at your code.

 

[Shotokan]

Hi @Shotokan

Could you upload your workbook or is there sensitive data on it?
If there is sensitive content, maybe you could replace it with some dummy data and upload it then.

Meanwhile I'll take a look at your code.

Hi PeteWright,

There is sensitive data on it yes but I can make a dummy no problem...
I'll be quick about it...

Thanks!

 

[Shotokan]

Hi @Shotokan

Could you upload your workbook or is there sensitive data on it?
If there is sensitive content, maybe you could replace it with some dummy data and upload it then.

Meanwhile I'll take a look at your code.

I have on ready for you but how do upload that? Looks like only mini sheets allowed?

 

[PeteWright]

I have on ready for you but how do upload that? Looks like only mini sheets allowed?

Do you have any cloud account like Dropbox, Box, Microsoft OneDrive, Google Drive, etc? You could upload it there.
Otherwise you can upload your sheet on Encl. Simply drag and drop files, select password in the setting menu and post the link.