Using Array Function to Alter Cell Values Based on Their Current Values?

Question

I asked a (similar question) a few weeks ago and I tried to navigate my way to my new question's answer using it but I have not been able to. Basically what I want to do is this: I have data being copied from a one workbook and pasted into column A of a template workbook. After that, I want to alter the contents of those cells that I just pasted based on what is in the cell when I paste them. For example, one cell will be pasted and it will say "ALBY Total" and I want to automatically (using VBA) change the cell to say "ALBY." I tried to code it myself but couldn't get it to work. I don't get an error, but nothing happens. Here's an example of my code (there are a ton of arrays, so I won't give you all of them):

Sub TSA_Template_Creation_Macro()

Workbooks("TSA Template.xlsm").Activate

Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

alby = Array("ALBY Total")
anch = Array("ANCH Total")
atlc = Array("ATLC Total")

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

For x = 1 To lastRow

    If stringIsInArray(ws.Cells(x, 1), alby) Then
        ws.Cells(x, 1) = "ALBY"
    ElseIf stringIsInArray(ws.Cells(x, 1), anch) Then
        ws.Cells(x, 1) = "ANCH (+office)"
    ElseIf stringIsInArray(ws.Cells(x, 1), atlc) Then
        ws.Cells(x, 1) = "ATLC"

 End If

Next x

End Sub

Function stringIsInArray(stringToBeFound As String, arr As Variant) As Boolean
stringIsInArray = (UBound(Filter(arr, stringToBeFound)) > 0)
End Function

I'm fairly new to the world of VBA so it could very well be a simple fix. I've fiddled around with it but I have no idea how to make it work.

JGrSmi · Accepted Answer · 2015-05-22 22:55:48Z

1

It sound like you are manually pasting the values. If that's the case the following should work when run from the template workbook containing the values:

Sub TSA_Template_Creation_Macro()

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For x = 1 To lastRow

If InStr(1, Cells(x, 1), "ALBY") Then
    Cells(x, 1) = "ALBY"
ElseIf InStr(1, Cells(x, 1), "ANCH") Then
    Cells(x, 1) = "ANCH (+office)"
ElseIf InStr(1, Cells(x, 1), "ATLC") Then
    Cells(x, 1) = "ATLC"

End If

Next x

End Sub

answered May 22, 2015 at 22:55

JGrSmi

494 bronze badges

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

1 Comment

chris7626 Over a year ago

You, sir, are a champion! Thank you so much!!! Like I said, I'm a bit of a neophyte working an internship and you saved my butt. This is the best website in the world!

Collectives™ on Stack Overflow

Using Array Function to Alter Cell Values Based on Their Current Values?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related