0

I am currently using the following in my code:

F = F & "," & I

This portion of code outputs my results in the following format in one single cell: 1130,1160,1190,1220,1250,1280,1310,1340,1370,1400,1430,1460,1490

I am wondering if there is a similar code that I could use that will enter each number in its own cell within a single column.

Community
  • 1
  • 1
kmja2500
  • 9
  • 2
  • 3
    There's no code that puts data anywhere except into the variable F. I would assume you have a loop to keep appending I so instead of just adding it to F just increment your row number and store it into the cell. – gtwebb Jul 20 '16 at 19:24
  • Possible duplicate of [VBA - Split string into individual cells](https://stackoverflow.com/questions/24814107/vba-split-string-into-individual-cells) – ashleedawg Feb 03 '18 at 13:59

1 Answers1

2

You use Split(F, ",") to create an array. Then use Range.Resize to make your destination range the same size as the array. Finally, make the new range = the array.

Sub Example()

    Dim F As String
    Dim a() As String

    F = "1130,1160,1190,1220,1250,1280,1310,1340,1370,1400,1430,1460,1490"
    a = Split(F, ",")

    Range("A1").Resize(1, UBound(a)) = a


End Sub
  • When I try to run this, it is giving me a 1004 run-time error for the following section of code >Range("A4").Resize(1, UBound(a)) = a – kmja2500 Jul 21 '16 at 15:31
  • I update my answer. The proper syntax is >Range("A1").Resize(1, UBound(a) + 1) = a –  Jul 21 '16 at 20:15
  • Ahh ok thanks! So now is there a way I can set F = to the cell containing the array so that if/when the numbers get changed the array the cells change with it? – kmja2500 Jul 21 '16 at 20:24
  • I'm really not sure what you want to do. You need to post more code. –  Jul 21 '16 at 20:30
  • Here is the full code for my function Function InBetween(First As Integer, Last As Integer) Dim i As Long, F As String, a() As String F = First For i = First + 30 To Last Step 30 F = F & "|" & i Next i InBetween = F End Function I am using this for scheduling parttime employees and using there availability as the first and last time to create a data validation that only contains times within their availability. – kmja2500 Jul 21 '16 at 20:42
  • Knowing availability will change I don't want to have to go through and manually change is in the code each time that occurs. So I am looking for a way to reference the cell that contains the times and split those. I was thinking maybe there is way to set F equal to a particular cell rather than have to set it equal to all the times manually. – kmja2500 Jul 21 '16 at 20:47
  • You can use a worksheet function to split the values of a cell. > =IF(ISERROR(FIND(" ",TRIM(A2),1)),TRIM(A2),MID(TRIM(A2),FIND(" ",TRIM(A2),1),LEN(A2))) [THis Post](http://stackoverflow.com/questions/1031305/simulate-string-split-function-in-excel-formula) might help –  Jul 21 '16 at 20:54