Code reduction

[carcharoth2554]

Hi guys,

Running Win 7 64 with Xl 2010, still getting into the flow of the code here.

I have working code here but it is sloppy....any suggestions on cleaning it up? I know I should be building in a loop to run through the cell references but my brain just cant quite get there!

Thanks!

Dim result As String
Dim resourceSheet As Worksheet
Dim resourceDest As Worksheet
Dim initialsArray(1 To 5) As String

If resourceDest.Range("E8") <> "" Then initialsArray(1) = Application.WorksheetFunction.VLookup(resourceDest.Range("E8"), resourceSheet.Range("A7:B26"), 2, False) & ", "
If resourceDest.Range("F8") <> "" Then initialsArray(2) = Application.WorksheetFunction.VLookup(resourceDest.Range("F8"), resourceSheet.Range("A7:B26"), 2, False) & ", "
If resourceDest.Range("G8") <> "" Then initialsArray(3) = Application.WorksheetFunction.VLookup(resourceDest.Range("G8"), resourceSheet.Range("A7:B26"), 2, False) & ", "
If resourceDest.Range("H8") <> "" Then initialsArray(4) = Application.WorksheetFunction.VLookup(resourceDest.Range("H8"), resourceSheet.Range("G7:H17"), 2, False) & ", "
If resourceDest.Range("I8") <> "" Then initialsArray(5) = Application.WorksheetFunction.VLookup(resourceDest.Range("I8"), resourceSheet.Range("G7:H17"), 2, False)

result = initialsArray(1) & initialsArray(2) & initialsArray(3) & initialsArray(4) & initialsArray(5)

.Cells(loopVertCount, 5) = result

 

[Norie]

Perhaps something like this.

Dim rng As Range
Dim I As Long

    Set rng = resourceDest.Range("E8")

    For I = 1 To 5

        If rng.Offset(, I - 1) <> "" Then
            initialsArray(I) = Application.WorksheetFunction.VLookup(rng.Offset(I - 1), resourceSheet.Range("A7:B26"), 2, False)
        End If

        result = initialsArray(1) & initialsArray(2) & initialsArray(3) & initialsArray(4) & initialsArray(5)
    Next I
    .Cells(loopVertCount, 5) = Join(initialsArray, ", ")

 

[carcharoth2554]

Perhaps something like this.

        If rng.Offset(, I - 1) <> "" Then
            initialsArray(I) = Application.WorksheetFunction.VLookup(rng.Offset(I - 1), resourceSheet.Range("A7:B26"), 2, False)
        End If

Thanks for the promt response! You guys are great!

Only problem is that I don't seem to be able to get the code to function, it keeps throwing back that nasty blank "Error 400" box. I don't seem to be able to get my head around where you were going with that 'IF' statement and how it is applying to the offset. Any ideas where it would be going wrong? I can post up the full script if needed.

Thanks!

 

[Norie]

The If is the same as your Ifs.

rng.Offset(, I - 1) refers to E8, F8, G8 etc as we go through the loop.

There is a typo in the code though, rng.Offset(I-1) in the VLookup needs to be rng.Offset(,I-1)

Dim rng As Range
Dim I As Long

    Set rng = resourceDest.Range("E8")

    For I = 1 To 5

        If rng.Offset(, I - 1) <> "" Then
            initialsArray(I) = Application.WorksheetFunction.VLookup(rng.Offset(,I - 1), resourceSheet.Range("A7:B26"), 2, False)
        End If
    Next I

    result = Join(initialsArray, ", ")

   .Cells(loopVertCount, 5) = result

 

[carcharoth2554]

There we go, gotcha! It was that stray comma I missed aswell!

Thanks, you are a star!