Search and find row on other sheet without selecting it

Shotokan

New Member
Joined
Jan 24, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

PeteWright

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jan 24, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Jan 24, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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

New Member
Joined
Dec 20, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,149
Members
415,881
Latest member
tasic89

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