VBA doubt: How to create a script to find data only in 2 columns for all worksheets?

RafaCAV

New Member
Joined
Mar 14, 2018
Messages
1
Hello!
I’m a chemical engineering post-graduate student and I’m not very good in programing, so I’m here to ask for help from specialists and enthusiasts.
I want to organize my lab reagents list to find then easily in the lab, using an Excel table. I would like to use a VBA script that acts like the Find (ctrl+f) function from Excel, which can find incomplete terms in a sequence, like “Find Next” button, but I need to restrict the search only to columns B and C, for several worksheets tabs.
I fold this script on the internet:

Sub Procura()

'Declara uma strign que vai receber o valor a ser procurado na planilha

Dim procurado As String

'Declara uma variável para receber o resultado de um MsgBox

Dim result As VbMsgBoxResult

'Declara duas variáveis do tipo inteiro

'i -> Para controlar o laço de repetição
'QuantPlanilhas -> para armazenar a quantidade de planilhas da pasta de trabalho atual

Dim i, QuantPlanilhas As Integer

'Atribui a quantidade de planilhas da pasta atual à variável QuantPlanilhas
'O método ThisWorkbook.Worksheets.Count retorna esse parâmetro

QuantPlanilhas = ThisWorkbook.Worksheets.Count

'Mostra um InputBox para que seja inserido o dado que será procurado em todas a
'Pasta de trabalho e atribui seu valor à variávle procurado

procurado = InputBox("Digite o valor a ser procurado", "Valor procurado", "Exemplo, 2, 3, uma data qualquer")

'Inicia o laço de repetição que varre todas as planilhas da pasta de trabalho atual

For i = 1 To QuantPlanilhas Step 1

'Seleciona toda a área da planilha

With Worksheets(i).Range("A:IV")

'Efetua o método de busca, que retorna o valor se for encontrado ou
'Nothing caso não encontre nada

Set c = .Find(procurado, LookIn:=xlValues)

'Caso tenha achado algo, ativa a célula onde está o valor procurado
'e oferece para o usuário se quer ou não continuar a busca

If Not c Is Nothing Then
Worksheets(i).Select
Range(c.Address).Select
result = MsgBox("Deseja continuar a busca?", vbYesNo, "Continuar?")

'Caso queira, continua a busca, caso contrário, sai do laço

If result = vbNo Then
Exit Sub
End If
End If
End With
Next
End Sub

This script wasn’t working well and it wasn’t what I really needed… but it was close to it.
I would like to know if it is possible to make a VBA script similar to the ctrl+f Find Excel function to operate only on the B and C columns for all my worksheets at my workbook and how to do it.
Thanks,
Rafa.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top