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


New Member
Mar 14, 2018
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
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
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.

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...