Dear All

I wonder if anyone can help at all.

I have a macro that populates an excel spreadsheet with data that it
retrieves from one of my company's mainframe systems.

After it does this, the macro puts a vlookup next to the data which adds a
category from a list held in a separate worksheet.

My problem is that it is hard to add new categories to that separate list.

What I mean is that to do this you have to change the range in the vlookup
formula and define a new range (with a new name) in the separate worksheet.

I tried to get around this by coding a form that adds the new category to
the end of the list in the separate workbook, then does a kind of manual
vlookup with the following code

Const TEST_COLUMN As String = "C"
Dim z As Long
Dim zLastRow As Long

Sheets("Accounts").Select
Range("A2").Select

Do

Sheets("Accounts").Select
Corpid = Left(ActiveCell.Value, 6)

With Worksheets("Corp Ids")

zLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For z = 2 To zLastRow 'zLastRow to 1 Step -1

If .Cells(z, "A").Value = Corpid Then

Sheets("Accounts").Select
ActiveCell.Offset(0, 4).Select
ActiveCell.Value = .Cells(z, TEST_COLUMN).Value
ActiveCell.Offset(0, -4).Select

End If

Next z

End With

Sheets("Accounts").Select
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = ""


Sheets("Accounts").Select
Range("f2").Select

Do
ActiveCell.Value = repdate
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

This is a very inelegant and problematic way of doing things.

Does anyone know of a better way.

I would be willing to make a contribution on behalf of my company for a good
solution.

Many thanks in advance nad kind regards

Nick

I can't really follow what you are doing, but you can use a dynamic named
range in the workbook containing the list - then use that named range in your
vlookup formula

Insert=Name=Define

Name: List1 ' for example
Refersto: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)

then your vlookup becomes like

=VLOOKUP(A6,DataWorkbookName.xls!List1,5,0)

This assumes both workbooks will be open.

--
Regards,
Tom Ogilvy


"Nick" wrote:

Dear All

I wonder if anyone can help at all.

I have a macro that populates an excel spreadsheet with data that it
retrieves from one of my company's mainframe systems.

After it does this, the macro puts a vlookup next to the data which adds a
category from a list held in a separate worksheet.

My problem is that it is hard to add new categories to that separate list.

What I mean is that to do this you have to change the range in the vlookup
formula and define a new range (with a new name) in the separate worksheet.

I tried to get around this by coding a form that adds the new category to
the end of the list in the separate workbook, then does a kind of manual
vlookup with the following code

Const TEST_COLUMN As String = "C"
Dim z As Long
Dim zLastRow As Long

Sheets("Accounts").Select
Range("A2").Select

Do

Sheets("Accounts").Select
Corpid = Left(ActiveCell.Value, 6)

With Worksheets("Corp Ids")

zLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For z = 2 To zLastRow 'zLastRow to 1 Step -1

If .Cells(z, "A").Value = Corpid Then

Sheets("Accounts").Select
ActiveCell.Offset(0, 4).Select
ActiveCell.Value = .Cells(z, TEST_COLUMN).Value
ActiveCell.Offset(0, -4).Select

End If

Next z

End With

Sheets("Accounts").Select
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = ""


Sheets("Accounts").Select
Range("f2").Select

Do
ActiveCell.Value = repdate
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

This is a very inelegant and problematic way of doing things.

Does anyone know of a better way.

I would be willing to make a contribution on behalf of my company for a good
solution.

Many thanks in advance nad kind regards

Nick

Many thanks this worked a treat!

"Tom Ogilvy" wrote:

I can't really follow what you are doing, but you can use a dynamic named
range in the workbook containing the list - then use that named range in your
vlookup formula

Insert=Name=Define

Name: List1 ' for example
Refersto: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)

then your vlookup becomes like

=VLOOKUP(A6,DataWorkbookName.xls!List1,5,0)

This assumes both workbooks will be open.

--
Regards,
Tom Ogilvy


"Nick" wrote:

Dear All

I wonder if anyone can help at all.

I have a macro that populates an excel spreadsheet with data that it
retrieves from one of my company's mainframe systems.

After it does this, the macro puts a vlookup next to the data which adds a
category from a list held in a separate worksheet.

My problem is that it is hard to add new categories to that separate list.

What I mean is that to do this you have to change the range in the vlookup
formula and define a new range (with a new name) in the separate worksheet.

I tried to get around this by coding a form that adds the new category to
the end of the list in the separate workbook, then does a kind of manual
vlookup with the following code

Const TEST_COLUMN As String = "C"
Dim z As Long
Dim zLastRow As Long

Sheets("Accounts").Select
Range("A2").Select

Do

Sheets("Accounts").Select
Corpid = Left(ActiveCell.Value, 6)

With Worksheets("Corp Ids")

zLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

For z = 2 To zLastRow 'zLastRow to 1 Step -1

If .Cells(z, "A").Value = Corpid Then

Sheets("Accounts").Select
ActiveCell.Offset(0, 4).Select
ActiveCell.Value = .Cells(z, TEST_COLUMN).Value
ActiveCell.Offset(0, -4).Select

End If

Next z

End With

Sheets("Accounts").Select
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Value = ""


Sheets("Accounts").Select
Range("f2").Select

Do
ActiveCell.Value = repdate
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

This is a very inelegant and problematic way of doing things.

Does anyone know of a better way.

I would be willing to make a contribution on behalf of my company for a good
solution.

Many thanks in advance nad kind regards

Nick