0

I have the following code, which i want to output on the worksheet, instead of the immediate window. Can someone help me with this?

Sub Fly()
    
    Dim r As Integer
    Dim c As Integer
    Dim Number As Single
    Number = 10002
    ReDim Flylist(1 To Number, 1 To 3) As String
    
  
    
    For r = 1 To Number
        For c = 1 To 3
            Flylist(r, c) = Cells(r + 1, c)
        Next c
    Next r
    
    For r = 1 To Number
        For c = 1 To 3
            If ((Cells(r + 1, 1) = "Luxembourg") Or (Cells(r + 1, 1) = "Warsaw") Or (Cells(r + 1, 1) = "Chicago")) _
            And ((Cells(r + 1, 2) = "Dusseldorf") Or (Cells(r + 1, 2) = "Faroe Islands")) Then 'Husk paranteser, i dette tilfælde dobbelt!
                Debug.Print Flylist(r, c)
                  
            End If
        Next c
    Next r
End Sub

This is the outut of the debug.print:

Chicago
Faroe Islands
DN3463

Warsaw
Faroe Islands
BC3497

Luxembourg
Faroe Islands
BF6959

Chicago
Dusseldorf
HK9783

Warsaw
Dusseldorf
VC1015
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
John
  • 1
  • how do you want it printed? In a column, like the current debug.print? – Scott Craner Aug 11 '21 at 21:18
  • BTW you can mass fill the Array: `Flylist = range(cells(2,1),cells(Number+1,3)).value` and remove the `ReDim` line. – Scott Craner Aug 11 '21 at 21:19
  • @ScottCraner I would like i to be listed from collum H through K – John Aug 11 '21 at 21:24
  • so basically you want it the same three columns wide and however long it comes to? – Scott Craner Aug 11 '21 at 21:26
  • Yes, 3 columms wide and as many rows as is needed – John Aug 11 '21 at 21:27
  • what version of Excel do you have. If you have office 365 Excel. Filter will do this with one formula. – Scott Craner Aug 11 '21 at 21:28
  • I have office 365 – John Aug 11 '21 at 21:28
  • Then put: `=FILTER(A:C,((A:A = "Luxembourg") + (A:A = "Warsaw") + (A:A = "Chicago"))*((B:B = "Dusseldorf") + (B:B = "Faroe Islands")))` in K2 and it will spill the results down. You may need to change the `,` to `;` and translate `FILTER` to your language depending on your local settings. – Scott Craner Aug 11 '21 at 21:34
  • Thank you! Is there a way to do it inside vba? – John Aug 11 '21 at 21:39
  • sure. `Range("H2").formula2 = "=FILTER(A:C,((A:A = ""Luxembourg"") + (A:A = ""Warsaw"") + (A:A = ""Chicago""))*((B:B = ""Dusseldorf"") + (B:B = ""Faroe Islands"")))"` will put that in the cell then you can do `Range("H:J").VAlue = Range("H:J").VAlue` and it will remove the formula and leave the values if you want to make them static. – Scott Craner Aug 11 '21 at 21:42
  • Thank you very much! Last question, is it posible to use a loop to output these values? – John Aug 11 '21 at 21:45
  • yes, instead of `Debug.Print Flylist(r, c)` do `Cells(r+1,c+8)=Flylist(r, c)` But it will be slower. – Scott Craner Aug 11 '21 at 21:47
  • I have already tried that, but it won't show on the worksheet :/ – John Aug 11 '21 at 21:50
  • you are implicitly using the activesheet you should include the worksheet parent to any range or cell object. See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba which probably the root of the issue. – Scott Craner Aug 11 '21 at 21:53
  • I am still totaly lost. It will print just fine in the immidiate window, but still no luck in getting them onto the worksheet – John Aug 12 '21 at 09:29

0 Answers0