VBA Offset

jamada · Nov 25, 2007

I have a spread sheet that the user enters 3 characters in B2 of a name, then the code will determine out of 35,000 rows with names in Column B how many match the 3 consecutive chracters, all other rows that dont match will hide.

My issue is, that the code seams to find the 3 leters only when there in the middle of a name, and will not identify all other simularities where the 3 letters are at the begining of the name, or at lerast not in all cases.

I adjusted the "Set tempCell = .Columns(2)." to (1) and that worked great but caused other problems.

Help is much appreciated ..........tks gg

lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
Set tempCell = .Range("B:B").Find(what:=Left(.Range("B2").Value, 3), After:=.Range("B2"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If tempCell Is Nothing Then
MsgBox "Not found"
Exit Sub
Else
Set foundCell = tempCell
Range(.Range("B3"), foundCell.Offset(-1, 0)).EntireRow.Hidden = True

End If
Do
Set tempCell = .Columns(2).FindNext(After:=foundCell)
If foundCell.Row >= tempCell.Row Then Exit Do
Range(foundCell.Offset(1, 0), tempCell.Offset(-1, 0)).EntireRow.Hidden = True
Set foundCell = tempCell
Loop
Range("A" & foundCell.Row + 1 & ":A" & lastrow).EntireRow.Hidden = True
End With
End Sub

saurabh726 · Nov 25, 2007

Hi...

What Value you are looking for.. i mean whts the word..??

jamada · Nov 25, 2007

Well the value coul be any 3 characters, for example "ECH" and then the code would locate "Pechmann" The issue seems to be when I search for "PEC" it will not locate Pehmann......many examples are simular.

I know if I chnage the 2 to 1 in the column it works Great BUT causes other erorr msgs such as "unable to get the find next property of the range class"

tks gg

saurabh726 · Nov 25, 2007

Two quick questions...

1...How do u determine which value to look for...is it static or its link to any particular cell which is varaible..?
2..As far i understand from your code if the value is find..u hide that row..right..??

stanleydgromjr · Nov 26, 2007

jamada,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Option Explicit
Sub Test()
    Dim lngLastRow As Long
    Dim c As Range
    If Range("B2") = "" Then
        MsgBox "Cell 'B2' is empty - macro terminated!"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C3").EntireColumn.Insert
    Range("C3:C" & lngLastRow).FormulaR1C1 = "=IF(ISERROR(FIND(R2C2,RC[-1],1)),0,FIND(R2C2,RC[-1],1))"
    For Each c In Range("C3:C" & lngLastRow)
      If c.Value > 0 Then c.EntireRow.Hidden = True
    Next c
    With Range("C3")
        .EntireColumn.Delete
    End With
    Range("B2").Select
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

jamada · Nov 26, 2007

Stanley thanks for post and solution......I have not had a chance to thank you earliar.

Option Explicit..........I have tried a few places in my code but honestly don't know where to place it!

Care to offer some direction.............

tks g

Fazza · Nov 27, 2007

Hi, Jamada.

Can you use either the autofilter or advanced filter for this? It can be easily programmed if you need and is fast. Looping through thousands of records is best avoided if possible.

regards, Fazza

stanleydgromjr · Nov 27, 2007

jamada,

Option Explicit..........I have tried a few places in my code but honestly don't know where to place it!

Care to offer some direction.............

In the VBA Editor,
click on Tools, Options
On the "Editor" tab, place a checkmark in the box for "Require Variable Declaration", and click on OK.

Fazza,

Yes, the code will run faster using Data Filter, however jamada will need to give us more information (screenshots of the worksheet).

jamada,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.

[url]http://www.puremis.net/excel/downloads.shtml
[/url]

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.

[url]http://www.excel-jeanie-html.de/index.php?f=1
[/url]

Have a great day,
Stan

jamada · Nov 27, 2007

Tks for the help Stanley/Fanzza

A B
8 009785 ABADJIAN,ADRINE
9 064823 ABATE,PAUL
10 092349 ABBALLE,MARISA
11 085228 ABBEY,ANNE DORTE
12 067395 ABBINANTE,GAETANO
13 063205 ABBOTT,DIANE GAIL
14 074853 ABBOTT,DOUGLAS CHISHOLM
15 007186 ABBOTT,JAMES
16 061376 ABBOTT,JUNE RITA CAVELLE
17 000414 ABBOTT,NATHALIE B
18 030281 ABBOTT,ROBERT CLARENCE
19 014315 ABDELJALIL,AILI
20 000215 ABDELLAOUI,ABDELLAH
21 008884 ABDELMASIH,JUDITH M
22 019969 ABDI,AHMED
23 027642 ABDINUR,MOHAMED
24 000200 ABDO,DOREEN L
25 000402 ABDULLA YASSIN
26 035061 ABDULLE,YUNIS HASSAN
27 001316 ABDULMALIK,AHMED
28 010088 ABE,CHIKAKO
29 000230 ABEAR,CHRISTOPHER J
30 000260 ABEBE,BOGALE T

Sheet1
[Table-It] version 06 by Erik Van Geit

Basically what I have is 35,0000 rows of data.

I expect the user to enter in B2 three (3) consecutive characters (Text) OF A NAME. (but would actually prefer it to be entered in a separate dialogue box i.e. msg box type)

Other data exists in each row, BUT only columns A and B are relevant.

The principle is quite simple, all of column b contains multiple names, last & first, sometimes an initial, both names are separated with a coma.

The idea is to find in column B, only matching names that contain the 3 consecutive characters that were entered in B2. I am looking for an name but unfortunately spelling is not always provided correctly, therefore it is easy to narrow it down by for example only looking for 3 consecutive characters.

I.E "Pechmann, Jacobb", if the user was to search on "Cob", "ann" or “Pec” it would be considered a hit and save the name, a none hit would "hide" the row.

Appreciate the help
tks g

stanleydgromjr · Nov 27, 2007

jamada,

Thanks for the data. The fact that the names are all uppercase makes a difference.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Option Explicit
Sub Test()
    Dim lngLastRow As Long
    Dim c As Range
    If Range("B2") = "" Then
        MsgBox "Cell 'B2' is empty - macro terminated!"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C3").EntireColumn.Insert
    Range("C8:C" & lngLastRow).FormulaR1C1 = "=IF(ISERROR(FIND(UPPER(R2C2),RC[-1],1)),0,FIND(UPPER(R2C2),RC[-1],1))"
    For Each c In Range("C8:C" & lngLastRow)
      If c.Value > 0 Then c.EntireRow.Hidden = True
    Next c
    With Range("C3")
        .EntireColumn.Delete
    End With
    Range("B2").Select
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

VBA Offset

jamada

Active Member

Excel Facts

saurabh726

Well-known Member

jamada

Active Member

saurabh726

Well-known Member

stanleydgromjr

Banned

jamada

Active Member

Fazza

MrExcel MVP

stanleydgromjr

Banned

jamada

Active Member

stanleydgromjr

Banned

Similar threads

Forum statistics

Share this page

VBA Offset

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Banned

Active Member

MrExcel MVP

Banned

Active Member

Banned

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock