VBA: Return Strings from For Function in 2 Columns

Question

I have no coding background but have always been curious to try it out. I've been enjoying it so far but have recently been stumped. I have exhausted my limited understanding and research on trying to figure this out, so any help is much appreciated!

My function takes a string and creates it into an array. Then I search the array for specific strings and output them as a number and a date. I have successfully made it this far but cannot figure out how to have VBA return these results in two columns on the spreadsheet. It seems to only give me the last result of the For function.

Here is the code using Excel 2016 and VBA:

    Function test() As Variant

    Dim data As String: data = "push.29Sep17 and 333>! push.28Sep17 and 55555>! push.27Sep17 and 4444>!"
    Dim day() As String: day() = Split(data, "!")
    Dim num As Integer: num = UBound(day) - 1

    For i = 0 To num
     Dim p1 As Integer: p1 = InStr(1, day(i), "and ")
     Dim p2 As Integer: p2 = InStr(p1, day(i), ">")
     Dim p3 As Integer: p3 = p1 + 4
     Dim p4 As Integer: p4 = p2 - p3
     Dim price1 As String: price1 = Mid(day(i), p3, p4)
     Dim d1 As Integer: d1 = InStr(1, day(i), "push.")
     Dim d2 As Integer: d2 = d1 + 5
     Dim date1 As String: date1 = Mid(day(i), d2, 7)
    test = price1 & " " & date1
     Debug.Print price1, date1
    Next i

    End Function

Here is what I'm getting when I use the function on a spreadsheet: test = price1 & " " & date1

Here is what I want to get:

Answer 1

User-Defined Functions (UDF's) are invoked by Excel when it calculates the worksheet.

You use them exactly like you use any other Excel worksheet function, e.g.:

=MyUDF("foo!bar")

Like every Excel worksheet function, UDF's take some input, do something with it, and return a result; Excel takes that result and displays it in the cell that invoked the function.

UDF's are explicitly forbidden to change anything in other cells, so it's impossible for a Function to affect any cell other than the cell that invoked it. Functions don't format cells, they compute a cell's value - nothing more, nothing less.

If you need some VBA code that needs to do more than that (e.g. affect the contents of multiple cells, format them, etc.), then what you need isn't a Function, but a macro - a parameterless Sub procedure that can be invoked from the macros menu, or attached to a button/shape on the sheet:

Public Sub DoSomething()
    Sheet1.Range("A1").Value = 42
    Sheet1.Range("A2").Formula = "=A1+12"
    Sheet1.Range("A1:A2").NumberFormat = "$#,##0.00"
End Sub

You'll quickly find out that the possibilities are practically endless - don't feel overwhelmed, we're here to help if research leads you nowhere and you're stuck on a specific problem, and know that there is Rubberduck (a VBIDE add-in OSS project I manage) that can help you avoid some common beginner (and some not-so-beginner) traps that you'll inevitably encounter (a lot of Rubberduck's code inspections were inspired by Stack Overflow questions!)

Enjoy your journey!

Answer 2

Here is the code that solves the problem:

    Sub test()

    Dim data As String: data = "push.29Sep17 and 333>! push.28Sep17 and 55555>! push.27Sep17 and 4444>!"
    Dim day() As String: day() = Split(data, "!")
    Dim num As Integer: num = UBound(day) - 1

    For i = 0 To num
     Dim p1 As Integer: p1 = InStr(1, day(i), "and ")
     Dim p2 As Integer: p2 = InStr(p1, day(i), ">")
     Dim p3 As Integer: p3 = p1 + 4
     Dim p4 As Integer: p4 = p2 - p3
     Dim price1 As String: price1 = Mid(day(i), p3, p4)
     Dim d1 As Integer: d1 = InStr(1, day(i), "push.")
     Dim d2 As Integer: d2 = d1 + 5
     Dim date1 As String: date1 = Mid(day(i), d2, 7)
     Sheet1.Range("A3:A5").Cells(i) = price1
     Sheet1.Range("B3:B5").Cells(i) = date1
    Next i

    End Sub

And the final result: