For loop with irregular step or use arrays if possible

[kelly mort]

Hi again,

I got something I think is a big problem for me but I don't know what you think:

I have a loop like

For i  = 1 To 100
 .........
Next

The loop is pointing to numbers in column B. However, all the numbers may not be present. For example, 2 or 54 etc, may not be available in the list. And in this case, the vlookup I am using will return that N/A .

If that alone, I would have a way to clear that. But I am also printing with the same loop as well.

Still I can set a condition to skip the print when that error shows up.

But the issue is, from that 1 To 100 used for the loop counter, there may be only two numbers found in the column B.

And in this case, running all the 100 counters seem to me as a waste of time and memory.

So it occurred to me that if I am able to load that column B to an array and then use the array index for the loop counter, then I can be able to take care of all the concerns I have raised above.

I hope someone has the cure for this.

Thanks

 

[offthelip]

you haven't given much detail about the restof your worksheet but something like this might help you:
I have loaded columns A and B into a varaint array and loop through the inut array and if the data exists in column copyu it to and output array and then afte the loop writethe output array to the worksheet.

Sub test()
inarr = Range("A1:B100")
Dim outarr(1 To 100, 1 To 2)
indi = 1
 For i = 1 To UBound(inarr, 1)
   If IsNumeric(inarr(i, 2)) And inarr(i, 2) <> "" Then
    For j = 1 To 2
     outarr(indi, j) = inarr(i, j)
    Next j
    indi = indi + 1
   End If
  Next i
  
Range("D1:E100") = outarr
End Sub

 

[kelly mort]

Cool.

Though I don't really understand all the lines well, I think this is the part that I need, with a little amendment , maybe, will help

Sub test()
inarr = Range("A1:B100")
Dim outarr(1 To 100, 1 To 2) 'I don't understand this line
indi = 1
 For i = 1 To UBound(inarr, 1) ' this place too
   If IsNumeric(inarr(i, 2)) And inarr(i, 2) <> "" Then
    
     ' the if statement is also not clear to me
      
      'my vlookup code will be here
       'The print out command will be here

   End If
  Next i
 
End Sub

So basically, all I need is get the array then use that for the counters.

Please if you need more details alert me.

Thanks

 

[offthelip]

I have annotated the code to help you:

Sub test()
inarr = Range("A1:B100")
Dim outarr(1 To 100, 1 To 2) 'This defines an output variant array with dimensions 100 by 2, this is equivalient to 100 rows by 2 columns


indi = 1
 For i = 1 To UBound(inarr, 1) ' this just says loop from 1 to the upper bound of the inarr array first dimension i.e 1 to 100
 
   If IsNumeric(inarr(i, 2)) And inarr(i, 2) <> "" Then ' this says if the value in columnB is numeric and is not blank then do the nextline
   
    
     ' the if statement is also not clear to me
      
      'my vlookup code will be here. I wouldn't use Vlookup I would do it all with vba
       'The print out command will be here. No the print command should be after you have written the data back to the worksheet.


   End If
  Next i
  
 Range("D1:E100") = outarr ' you missed out this line which writes the data back to the worksheet, you must write it somewhere in order to print, this could be a temp worksheet
' prints statement to do go here


End Sub

to see how to do VLOOKUP using vBA instead see this thread:
https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[kelly mort]

Actually let me get back to the original for loop that I have.

For i = 1 To 100
Sheet2.[B5] = Application. Vlookup(i, Sheet1.[B1:F100],2,0)
Sheet2.PrintOut
Next

So I do print for each counter.

I hope you are seeing the picture now.

 

[offthelip]

I don't quite understand why you printout sheet2 for every mathc but this code does same as your code ( I think I haven't tested it!!). It uses variant array to do the vlookup which is much much faster

'load all the data into an array
datar = Worksheets("Sheet1").Range("B1:f100")


For i = 1 To 100
   ' this bit is doing the vlookup by searching through the data array for a match
  For j = 1 To 100
    If datar(j, 1) = i Then ' this is checkign for a match
         ' mathc foujnd to assgn column C to B5
         Sheet2.[B5] = datar(j, 2)
         Sheet2.PrintOut ' print our sheet 2
         Exit For
    End If
  Next j
Next i

 

[kelly mort]

I don't quite understand why you printout sheet2 for every mathc but this code does same as your code ( I think I haven't tested it!!). It uses variant array to do the vlookup which is much much faster

'load all the data into an array
datar = Worksheets("Sheet1").Range("B1:f100")


For i = 1 To 100
   ' this bit is doing the vlookup by searching through the data array for a match
  For j = 1 To 100
    If datar(j, 1) = i Then ' this is checkign for a match
         ' mathc foujnd to assgn column C to B5
         Sheet2.[B5] = datar(j, 2)
         Sheet2.PrintOut ' print our sheet 2
         Exit For
    End If
  Next j
Next i

Okay, I am not by my PC ATM.

I will test it and report back to you.

I can see your code is looking sweeter in the process.

 

[kelly mort]

I don't quite understand why you printout sheet2 for every mathc but this code does same as your code ( I think I haven't tested it!!). It uses variant array to do the vlookup which is much much faster

'load all the data into an array
datar = Worksheets("Sheet1").Range("B1:f100")


For i = 1 To 100
   ' this bit is doing the vlookup by searching through the data array for a match
  For j = 1 To 100
    If datar(j, 1) = i Then ' this is checkign for a match
         ' mathc foujnd to assgn column C to B5
         Sheet2.[B5] = datar(j, 2)
         Sheet2.PrintOut ' print our sheet 2
         Exit For
    End If
  Next j
Next i

I just tested the code and it didn't work the way I wanted .

It was not returning anything into the cell B5 until I changed my range to "B1:F10" to test what was actually going on.

Then I have to also have the numbering in column B from 1-10.

Then when I ran the code, it returned the last item in column C on sheet1 into sheet2 cell B5.

So I decided to change the location of the "Exit For" and it seems to return the first item this time.

But the issue is that as I said before, the numbers in column B on sheet1 will not be in a complete counting number order. Some may be missing , the reason I was thinking if I could load that to an array then be able to use the array index for my counters, it will be great.

After I changed the location of that "Exit For" as said above, I tried to test further, replacing the "1" with "0" in the number list in column B sheet1. And this time, the code failed to return something.

And I reversed the location of the "Exit For" again, while keeping the zero as the first item in the number list and the code returned the last item again.

'load all the data into an array
datar = Worksheets("Sheet1").Range("B1:f10")


For i = 1 To 10
   ' this bit is doing the vlookup by searching through the data array for a match
  For j = 1 To 10
    If datar(j, 1) = i Then ' this is checkign for a match
         ' mathc foujnd to assgn column C to B5
         Sheet2.[B5] = datar(j, 2)
         Sheet2.PrintOut ' print our sheet 2
         'Exit For
    End If
  Next j
  Exit For
Next i

Then there is this part, in case I have say 10 or more individual lines to write, can it be made simple?

Example

Sheet2.[B5] = datar(j, 2)
Sheet2.[B6] = datar(j, 3)
Sheet2.[B7] = datar(j, 4)

To answer your question about why I am printing for each counter, it's because I am using the vlookup , as I had before, to load sheet2 with data based on the counter. And each counter represents a unique report.

Thanks

 

[mikerickson]

Your original code could be summaraized by

' pseudo-code

For i = 1 to 100
    If i is OK then
        Do something
    End If
Next I

The put in some array approach would be

For i = 1 to 100
    If i is OK then
        add i to myArray
    End If
Next i

For each oneEntry in myArray
    do something
Next oneEntry

You are increasing the number of loops.

 

[kelly mort]

Your original code could be summaraized by

' pseudo-code

For i = 1 to 100
    If i is OK then
        Do something
    End If
Next I

The put in some array approach would be

For i = 1 to 100
    If i is OK then
        add i to myArray
    End If
Next i

For each oneEntry in myArray
    do something
Next oneEntry

You are increasing the number of loops.

Okay, cool. I am getting the picture now.

So let say I have the numbers 34, 5, 27 in the column B.

So I load them to the array with the first loop (the For Loop) - which I have no idea how to do that.

Then I get the array index 0, 1, 2

Inside the For Each Loop, the "do something" line, how do I make it happen ?

Based on the original approach I used to do my vlookup?