JOHNNATH
12-08-2012 21:47:19
estou com um Problemão tenho uma planilha (Geral) e nelas tenho varias abas (20) o que gostaria é que quando eu digitar um nome na minha Testbox1 ele buscasse estes valores da busca em todas as 20 abas. buscando cada valor idêntico as suas informações. ate onde conseguir fazer foi apenas busca o valor para uma aba as demais não.
com este modelo abaixo que encontrei no forun
Private Const NomePlanilha As String = "EFETIVO"
Private Const LinhaCabecalho As Integer = 1
'Autor: Tomás Vásquez. - Santo André-SP-BR
' tomas.vasquez.sites.uol.com.br
' tomas.vasquez.blog.uol.com.br
' tomas.vasquez@uol.com.br
'Data: 15 de Janeiro de 2007.
'Versão: 1.00 - Em VBA Microsoft Excel 2003
'Fórum: http://www.juliobattisti.com.br/forum
' [Excel Avançado - Macros e Vba]
Private TextoDigitado As String
Private Sub busca_Click()
'If Me.ComboBoxCampos.ListIndex <> -1 Then
Call PreencheLista(TextBox1.Text)
' End If
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub userform_Initialize()
' Call PreencheLista
End Sub
Private Sub PreencheLista(ByVal TextoDigitado As String)
Dim ws As Worksheet
Dim i As Integer
Dim X As Integer
Dim indiceLista As Integer
Dim coluna As Integer
Dim TextoCelula As String
Set ws = ThisWorkbook.Worksheets(NomePlanilha)
Dim Lista()
ReDim Lista(ws.UsedRange.Columns.Count, 0)
i = LinhaCabecalho + 1
indiceLista = 1
coluna = TextBox6
Call PreencheCabecalho(Lista)
ListBox1.Clear
With ws
While .Cells(i, coluna).Value <> Empty
TextoCelula = .Cells(i, coluna).Value
If UCase(Left(TextoCelula, Len(TextoDigitado))) = UCase(TextoDigitado) Then
For X = 0 To ws.UsedRange.Columns.Count - 1
ReDim Preserve Lista(ws.UsedRange.Columns.Count, indiceLista)
Lista(X, indiceLista) = .Cells(i, X + 1)
Next
indiceLista = indiceLista + 1
End If
i = i + 1
Wend
End With
Lista = Array2DTranspose(Lista)
Me.ListBox1.List = Lista
End Sub
Private Sub PreencheCabecalho(ByRef Lista())
Dim ws As Worksheet
Dim coluna As Integer
Dim linha As Integer
Set ws = ThisWorkbook.Worksheets(NomePlanilha)
coluna = 1
linha = LinhaCabecalho
With ws
While .Cells(linha, coluna).Value <> Empty
Lista(coluna - 1, 0) = .Cells(linha, coluna)
coluna = coluna + 1
Wend
End With
End Sub
Function Array2DTranspose(avValues As Variant) As Variant
Dim lThisCol As Long, lThisRow As Long
Dim lUb2 As Long, lLb2 As Long
Dim lUb1 As Long, lLb1 As Long
Dim avTransposed As Variant
If IsArray(avValues) Then
On Error GoTo ErrFailed
lUb2 = UBound(avValues, 2)
lLb2 = LBound(avValues, 2)
lUb1 = UBound(avValues, 1)
lLb1 = LBound(avValues, 1)
ReDim avTransposed(lLb2 To lUb2, lLb1 To lUb1)
For lThisCol = lLb1 To lUb1
For lThisRow = lLb2 To lUb2
avTransposed(lThisRow, lThisCol) = avValues(lThisCol, lThisRow)
Next
Next
End If
Array2DTranspose = avTransposed
Exit Function
ErrFailed:
Debug.Print Err.Description
Debug.Assert False
Array2DTranspose = Empty
Exit Function
Resume
End Function
Sub PESQUISA()
Dim PrimEndereço, C As Range
With Sheets("EFETIVO").Range("a2:a65000")
Set C = .Find(TextBox2.Text, LookIn:=xlValues, LOOKAT:=xlPart, MatchCase:=False)
If Not C Is Nothing Then
PrimEndereço = C.Address
Do
Me.TextBox3 = Sheets("EFETIVO").Range("B" & C.Row).Value
Me.TextBox4 = Sheets("EFETIVO").Range("C" & C.Row).Value
Me.TextBox5 = Sheets("EFETIVO").Range("D" & C.Row).Value
Set C = .FindNext(After:=C)
Loop While Not C Is Nothing And C.Address <> PrimEndereço
End If
End With
End Sub
Tem um outro codigo que encontrei que faz o que preciso porem ele da o valor da celula e não os dados da celula
Private Sub CommandButton1_Click()
Dim currentFind As Excel.Range
Dim firstFind As Excel.Range
Dim lQtdePlan As Integer
Dim lPlanAtual As Integer
Dim lPlanFim As Boolean
lQtdePlan = Worksheets.Count
lPlanAtual = 1
lPlanFim = False
listResultado.Clear
While lPlanAtual <= lQtdePlan
Set currentFind = Worksheets(lPlanAtual).Range("A1:XFD1048576").Find(txtPesquisa.Text, , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
Set firstFind = Nothing
While Not currentFind Is Nothing And lPlanFim = False
If firstFind Is Nothing Then
Set firstFind = currentFind
ElseIf currentFind.Address = firstFind.Address Then
lPlanFim = True
End If
If lPlanFim = False Then
Set currentFind = Worksheets(lPlanAtual).Range("A1:XFD1048576").FindNext(currentFind)
listResultado.AddItem (Worksheets(lPlanAtual).Name & "!" & currentFind.Address)
End If
Wend
lPlanAtual = lPlanAtual + 1
lPlanFim = False
Wend
Sair:
Exit Sub
End Sub
Private Sub listResultado_Click()
Dim lRng As Range
Dim lEndereco() As String
lEndereco = Split(listResultado, "!")
Worksheets(lEndereco(0)).Activate
Set lRng = Range(lEndereco(1))
lRng.Activate
End Sub
alguem poderia me ajudar
com este modelo abaixo que encontrei no forun
Private Const NomePlanilha As String = "EFETIVO"
Private Const LinhaCabecalho As Integer = 1
'Autor: Tomás Vásquez. - Santo André-SP-BR
' tomas.vasquez.sites.uol.com.br
' tomas.vasquez.blog.uol.com.br
' tomas.vasquez@uol.com.br
'Data: 15 de Janeiro de 2007.
'Versão: 1.00 - Em VBA Microsoft Excel 2003
'Fórum: http://www.juliobattisti.com.br/forum
' [Excel Avançado - Macros e Vba]
Private TextoDigitado As String
Private Sub busca_Click()
'If Me.ComboBoxCampos.ListIndex <> -1 Then
Call PreencheLista(TextBox1.Text)
' End If
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub userform_Initialize()
' Call PreencheLista
End Sub
Private Sub PreencheLista(ByVal TextoDigitado As String)
Dim ws As Worksheet
Dim i As Integer
Dim X As Integer
Dim indiceLista As Integer
Dim coluna As Integer
Dim TextoCelula As String
Set ws = ThisWorkbook.Worksheets(NomePlanilha)
Dim Lista()
ReDim Lista(ws.UsedRange.Columns.Count, 0)
i = LinhaCabecalho + 1
indiceLista = 1
coluna = TextBox6
Call PreencheCabecalho(Lista)
ListBox1.Clear
With ws
While .Cells(i, coluna).Value <> Empty
TextoCelula = .Cells(i, coluna).Value
If UCase(Left(TextoCelula, Len(TextoDigitado))) = UCase(TextoDigitado) Then
For X = 0 To ws.UsedRange.Columns.Count - 1
ReDim Preserve Lista(ws.UsedRange.Columns.Count, indiceLista)
Lista(X, indiceLista) = .Cells(i, X + 1)
Next
indiceLista = indiceLista + 1
End If
i = i + 1
Wend
End With
Lista = Array2DTranspose(Lista)
Me.ListBox1.List = Lista
End Sub
Private Sub PreencheCabecalho(ByRef Lista())
Dim ws As Worksheet
Dim coluna As Integer
Dim linha As Integer
Set ws = ThisWorkbook.Worksheets(NomePlanilha)
coluna = 1
linha = LinhaCabecalho
With ws
While .Cells(linha, coluna).Value <> Empty
Lista(coluna - 1, 0) = .Cells(linha, coluna)
coluna = coluna + 1
Wend
End With
End Sub
Function Array2DTranspose(avValues As Variant) As Variant
Dim lThisCol As Long, lThisRow As Long
Dim lUb2 As Long, lLb2 As Long
Dim lUb1 As Long, lLb1 As Long
Dim avTransposed As Variant
If IsArray(avValues) Then
On Error GoTo ErrFailed
lUb2 = UBound(avValues, 2)
lLb2 = LBound(avValues, 2)
lUb1 = UBound(avValues, 1)
lLb1 = LBound(avValues, 1)
ReDim avTransposed(lLb2 To lUb2, lLb1 To lUb1)
For lThisCol = lLb1 To lUb1
For lThisRow = lLb2 To lUb2
avTransposed(lThisRow, lThisCol) = avValues(lThisCol, lThisRow)
Next
Next
End If
Array2DTranspose = avTransposed
Exit Function
ErrFailed:
Debug.Print Err.Description
Debug.Assert False
Array2DTranspose = Empty
Exit Function
Resume
End Function
Sub PESQUISA()
Dim PrimEndereço, C As Range
With Sheets("EFETIVO").Range("a2:a65000")
Set C = .Find(TextBox2.Text, LookIn:=xlValues, LOOKAT:=xlPart, MatchCase:=False)
If Not C Is Nothing Then
PrimEndereço = C.Address
Do
Me.TextBox3 = Sheets("EFETIVO").Range("B" & C.Row).Value
Me.TextBox4 = Sheets("EFETIVO").Range("C" & C.Row).Value
Me.TextBox5 = Sheets("EFETIVO").Range("D" & C.Row).Value
Set C = .FindNext(After:=C)
Loop While Not C Is Nothing And C.Address <> PrimEndereço
End If
End With
End Sub
Tem um outro codigo que encontrei que faz o que preciso porem ele da o valor da celula e não os dados da celula
Private Sub CommandButton1_Click()
Dim currentFind As Excel.Range
Dim firstFind As Excel.Range
Dim lQtdePlan As Integer
Dim lPlanAtual As Integer
Dim lPlanFim As Boolean
lQtdePlan = Worksheets.Count
lPlanAtual = 1
lPlanFim = False
listResultado.Clear
While lPlanAtual <= lQtdePlan
Set currentFind = Worksheets(lPlanAtual).Range("A1:XFD1048576").Find(txtPesquisa.Text, , _
Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
Set firstFind = Nothing
While Not currentFind Is Nothing And lPlanFim = False
If firstFind Is Nothing Then
Set firstFind = currentFind
ElseIf currentFind.Address = firstFind.Address Then
lPlanFim = True
End If
If lPlanFim = False Then
Set currentFind = Worksheets(lPlanAtual).Range("A1:XFD1048576").FindNext(currentFind)
listResultado.AddItem (Worksheets(lPlanAtual).Name & "!" & currentFind.Address)
End If
Wend
lPlanAtual = lPlanAtual + 1
lPlanFim = False
Wend
Sair:
Exit Sub
End Sub
Private Sub listResultado_Click()
Dim lRng As Range
Dim lEndereco() As String
lEndereco = Split(listResultado, "!")
Worksheets(lEndereco(0)).Activate
Set lRng = Range(lEndereco(1))
lRng.Activate
End Sub
alguem poderia me ajudar