Excel VBA - Tricky Indexing Logic to VBA

Question

I have run into an issue with some logic in VBA which I have found in my experience very tricky to figure out, I will attempt to explain it below.

I have 4 columns in my worksheet, structured as follows

|    A     |  |    B    |  |  C |  |  D | 
|First Name|  |Last Name|  |Type|  |Text|

I am in search of some VBA which can effectively scan the First Name column, once it encounters a blank, it initiates the following logic

Look in the Type (C) Column

If it encounters text in the type column that says Journal then check the cell located in the same row in the Text (D) column

Take the free text in the text column, put it into an array.

Use the text in the array and scan the First Name column for matching text entries, if it finds a match then take the matching text in the Text column and paste it into the First Name column. Do this until the array runs out.

Restart step 4 for the Last Name column

Loop back to step 1

NOTES: The text in the Text column is a free text extract from a third party application (SAP), therefore I am running off the assumption that if it contains a First Name or Last Name, it can match it with the entries already located in the First Name and Last Name column

I have been looking everywhere for syntax which can do this efficiently but I am stuck with writing the logic for this and need some advice from the community, any ideas?

UPDATE: An example of the data would be like this

|     A       |   |      B      |   |     C      |   |     D           |
| First Name  |   |  Last Name  |   |   Type     |   |   Text          |
| Michael     |   |  Jackson    |   |    WE      |   |  SAP CATS       |

|             |   |             |   |    SS      |   |  CATS O/H Michael Jackson|

I might be missing something, but it seems like the above logic will accomplish absolutely nothing. If you paste over data with exactly what was already there, what are you accomplishing? — Ben Strombeck
– Ben Strombeck, Commented Mar 4, 2015 at 5:45
It would help if I gave some context, the data above is actually a mirror of an extract of timesheeting and accrual data from SAP. If the person does a timesheet (michael jackson), the persons name comes up in the first name / last name column. However if its an accrual for michael jacksons time, it doesn't come up as a timesheet entry, rather it comes up in Column D as a free text entry. Its a disconnect in the way SAP records data as accruals are not considered timesheet entries. I need to normalize it in order to achieve an automated extract to a financial tool which I am producing. — PootyToot
– PootyToot, Commented Mar 4, 2015 at 5:47
Ahh, so on that second line, you're wanting the code to actually put the Michael into Column A, and the Jackson into column B? (But only if the type is Journal) — Ben Strombeck
– Ben Strombeck, Commented Mar 4, 2015 at 5:50
Thats it! It needs to ignore the CATS O/H or any other pointless text aside from teh first name / last name. It needs to happen with RA (reverse accrual), AC (Accrual) and SS (Journals) — PootyToot
– PootyToot, Commented Mar 4, 2015 at 5:51

CustodianOfCode · Accepted Answer · 2015-03-04 06:09:27Z

2

Not really sure why you want to do it, but here is vba that implements the logic you have described:

Sub t()
    For i = 2 To Range("A50000").End(xlUp).Row
        '1. Look in the Type (C) Column
        typ = Range("C" & i).Value

        '2. If it encounters text in the type column that says Journal then...
        If typ = "Journal" Then

            '...check the cell located in the same row in the Text (D) column
            txt = Range("D" & i).Value

            '3. Take the free text in the text column, put it into an array.
            txtArray = Split(txt, " ")

            '4. Use the text in the array and scan the First Name column for matching
            'text entries, if it finds a match then take the matching text in the
            'Text column and paste it into the First Name column. Do this until the
            'array runs out.

            For Each t In txtArray
                For j = 2 To Range("A50000").End(xlUp).Row
                    If Range("A" & j).Value = t Then
                        match_txt = Range("D" & j).Value
                        Range("A" & i).Value = match_txt
                    End If
                Next j
            Next t

            '5. Restart step 4 for the Last Name column
            'note: i would just do this in the above loop.
            'split out here so that you can see step 5 seperately from step 4
            For Each t In txtArray
                For j = 2 To Range("A50000").End(xlUp).Row
                    If Range("B" & j).Value = t Then
                        match_txt = Range("D" & j).Value
                        Range("B" & i).Value = match_txt
                    End If
                Next j
            Next t

        End If

    '6. loop back to step 1
    Next i

End Sub

answered Mar 4, 2015 at 6:09

CustodianOfCode

6743 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ben Strombeck Over a year ago

this is really close. All you need to do to fix this is change the line of code that says "match_txt = Range("D" & j).Value" to "match_txt = t" (it's on there twice).

Ben Strombeck Over a year ago

Also, the first line of code -- For i = 2 To Range("A50000").End(xlUp).Row -- uses column A to determine the last used column, which is probably not reliable. Might be better to use column C, or otherwise just make sure to type something at the very bottom of column A to make sure every row is processed

PootyToot Over a year ago

This is very very close, it certainly does well, right up until this line here match_txt = Range("D" & j).Value It seems to be not copying the name, rather it copies a piece of text that might be 10 rows down from the target, its confusing as to why. Trying to work that out on my end.

Ben Strombeck Over a year ago

@PootyToot, did you get it to work with what I said? It worked for me when I replaced that one line

Sheridan Gray · Accepted Answer · 2015-03-04 06:01:30Z

I don't think I really understand the Text column and I'm not really around a computer with real Excel (Mac Excel sucks) but off the top of my head I'd approach it something like this.

Dim wSheet1 as Worksheet

Sub main()
    Dim i As Long

    Set wSheet1 = Sheets(wSheet1)

    For i = 2 To getLastUsedRow(wSheet1)

        # Requirements #1 and #2
        If wSheet1.Cells(i, "A") = "" And wSheet1.Cells(i, "C") = "Journal" Then
            processText(i, wSheet1.Range("A:A"))
        End If

        # Requirement #5
        If wSheet1.Cells(i, "B") = "" And wSheet1.Cells(i, "C") = "Journal" Then
            processText(i, wSheet1.Range("B:B"))
        End If
    Next i
End Sub

Sub processText(i as Long, searchCol As Range)
    Dim words as Variant
    text = wSheet1.Cells(i, "D")

    # Requirement #3
    words = Split(text, " ")

    For j = 0 To UBound(words)
        word = words(i)
        vTest = Application.VLookup(word, searchCol, 1, False)
        If IsError(vTest) = False Then
            # Requirement #4
            wSheet1.Cells(i, "A") = word
        End If
    Next j
End Sub

Function getLastUsedRow(w as Worksheet)
    getLastUsedRow = w.UsedRange.Rows.Count
End Function

Collectives™ on Stack Overflow

Excel VBA - Tricky Indexing Logic to VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related