Need help with Offset and Match in VBA 1

[ladyemrys]

Hi!

I was given a pair of spreadsheets that has some custom VB code applied to the main sheet. Currently, the code allows them to pull up the member name by putting in the pass number. What they need is the member name to pull up the number and place the data in the proper cell. I'm new to VB and have been unable to make it work properly at all. Any help is much appreciated.

The main form looks like this (i've changed the last names as these are citizens of the city i work for):


Court 1
Time Code Card # Name Res NR
8am 7809 Nxxx, Keith

The form it pulls the data from looks like this:

Member
Member Name Pass #

AAAAz, Esteban 9636

AAAAn, Lucienne 8921

ABBBB, Erin 9446

ABDDD, Tina 8032

ABRRRR, Stanley C 8020

Here is the code i was given:

Public Function IsWorkbookOpen( _
ByVal WorkbookName As String _
) As Boolean

Dim Workbook As Workbook

For Each Workbook In Workbooks
If Workbook.Name = WorkbookName Then
IsWorkbookOpen = True
Exit Function
End If
Next Workbook

End Function

Private Sub Worksheet_Change(ByVal Target As Range)

Dim FocusRange As Range
Dim Cell As Range
Dim Row As Long
Dim SourceTable As Range

Set FocusRange = Intersect([c11:c92,i11:i92,o1192,u11:u92,aa11:aa92,ag11:ag92], Target) ' Change the range on this line to include all cells in which an account number can be entered
If Not FocusRange Is Nothing Then
If Not IsWorkbookOpen("Customers.xls") Then
Application.ScreenUpdating = False
Workbooks.Open ThisWorkbook.Path & "\Customers.xls", 2, True
ThisWorkbook.Activate
Application.ScreenUpdating = True
End If

Set SourceTable = Workbooks("Customers.xls").Sheets(1).[A2:F1500]
For Each Cell In FocusRange
If Len(Cell) > 0 Then
Row = 0
On Error Resume Next
Row = Application.Match(Cell, SourceTable.Columns(6), 0)
On Error GoTo 0
If Row > 0 Then
Cell.Offset(ColumnOffset:=1) = SourceTable.Columns(1).Cells(Row)
Else
Cell.Offset(ColumnOffset:=1) = "Not Found"
End If
End If
Next Cell
End If

End Sub

 

[SkipVought]

Hi,

"The form it pulls the data from looks like this"

What sheet/range is this data on?

You can lookup the Member_Name and return the Pass #. Using Named Ranges...

dim xl as application
set xl = application

...
sPassNbr = xl.Index([Pass],xl.match(sMemName,[Member_Name],0),1)
...

set xl = nothing

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ladyemrys]

Thanks! I will try your suggestion right now

 

[ladyemrys]

Hi Skip!

I've posted the two related spreadsheets here:

http://www.tripledivas.com/VB/Customers.xls

http://www.tripledivas.com/VB/Copy_of_AprilOV_New.xls

I think i bit off way more than i can chew with this project, if you can point me in the right direction, I would very much appreciate it. I did try to work with your earlier suggestion, but couldn't get it to work right either.

Thanks and have a great day.
Sabrina

 

[SkipVought]

You've made the mistake that many spreadsheet users make: Storing data in a REPORT (the way that you want to SEE the data displayed) rather than in one or more TABLES.

The data in these sheets is nearly useless as a source for data analyis and reporting.

Memberships by Customer (Summary) is virtually useless without some major reconstruction: A table with ONE ROW OF HEADINGS, NO EMPTY ROWS or COLUMNS
[tt]
Member Name Pass # Age Gndr Res Home Phone Package Name Receipt # Prim Sold Effective Expires Total Due
139Lopez, Esteban 9636 40.11 M Y (310) 459-7877 Tennis Res. 1001886.011 Y 04/15/07 04/15/07 06/30/07 $0.00
Aarsen, Lucienne 8921 43.4 F Y (310) 451-1502 Tennis Res. 1000376.011 Y 02/01/07 02/01/07 06/30/07 $0.00
Abbott, Erin 9446 25.6 Y (310) 779-1198 Tennis Res. 1001512.011 Y 03/30/07 03/30/07 06/30/07 $0.00
[/tt]
This is where you can use the LOOKUP I posted above.

Then you have Current, Current(2)... Curent(32) 43 sheets, for what?

Without a Schedule Table, you are going to have to punch in every swinging member competing.

You'll need a Member Table, Resources Table, Schedule Table.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ladyemrys]

Thanks Skip!

I will first hash out your great suggestions - that makes it nice and orderly, like a real database. That certainly makes it easier for me to understand and will make it much easier to work with. I didn't create these monstrosities, i got dragged into it because i work the help desk part time...

Thanks again, YOU ROCK
Sabrina

 

[SkipVought]

Reports are hardly ever a good source for data. If this is a one-shot deal, just sort the entire sheet to get all the data rows togethe and then delete the extraneous rows and columns.

If, however, you get reports regularly, either find a way to get to the data that the report was generated from, where you could actually QUERY the source data, or write code to convert the report to a table.

The effort will be well worth it!

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[ladyemrys]

Thanks Skip YOU ARE WONDERFUL!

You are absolutely right, I'm starting to get more and more reports (and once the user sees the final product, i'm sure she's going to want more converted), so your advice is going to go a long way for me. After I read your post yesterday, I figured I'd use access to create the database then i can work with it from there

Thanks again.
Sabrina