For Loop in Excel

[boyd108]

Hello,

I'm attempting to loop through a selection in Excel written with VBA. I select a range of cells and then select all non-blank cells in that range. So far, so good.

I then run a For loop through the selection.The intended result is that for each cell in the selection, the value of the first cell in the current column will be appended to the front of the cell. The loop runs for the number of items selected, but I am having trouble having it run on each cell in the selection. Instead, it just runs the whole thing on the first active cell of the selection, writing something like

$header1$$header1$$header1$$header1$$header1$$header1$$header1$$header1$$header1$$header1$cell1

What I need to know is how do I reference the next cell in the selection as I loop through? The below code is wrong, I know, because it produces the result above. I'm not sure what to replace ActiveCell with or how to modify this. Thanks for your help!

$openDiff.ActiveWorkbook.Sheets(1).Range("F2", $openDiff.Application.Selection.SpecialCells(11)).Select
  $openDiff.Application.Selection.SpecialCells(2,23).Select
  For $cell in $openDiff.Application.Selection
   $openDiff.Application.ActiveCell.Value = $openDiff.Application.Cells(1, $openDiff.Application.ActiveCell.Column).Value & $openDiff.Application.ActiveCell.Value
  Next

[boyd108]

I should note, I will not use UDFs for this. The UDFs are too slow for the file sizes this is intended for. I used UDFs before, but it took over an hour for a 20k line file. I absolutely must write this using VBA in AutoIt.

Sorry to make it so difficult

[Beege]

Are you sure the UDF is slow or was your code not written correctly? I've used our excel udf to read in, modify, and write back thousands of elements and never had a problem.

Just glancing at your For loop I'm seeing you never use $cell in your statement. $cell is the item. Each loop goes on to the next $cell in the collection returned from $openDiff.Application.Selection. So $cell is how you get to your next item.

[boyd108]

Wow I made it far more complicated than I needed. I didn't $cell.Value would work. I thought I needed to reference $openDiff.Application.etc first. Thanks! That solved my problem.

As to my code being wrong or poorly written when using the UDF, this is the code I was using before.

for $x=2 to $rowsDiffUsed
  $guidNum = _ExcelReadCell($openDiff, $x, 1)
   $data1 = $openSnapshot.Application.WorksheetFunction.VLOOKUP( $guidNum, $openSnapshot.Range("A1:G80000"), 7, FALSE )
   $data2 = $openSnapshot.Application.WorksheetFunction.VLOOKUP( $guidNum, $openSnapshot.Range("A1:G80000"), 6, FALSE )
  _ExcelWriteCell($newDiff, $data1, $x, 2) ;Item Name
  _ExcelWriteCell($newDiff, $data2, $x, 3) ;Item ID
  $changeCell = 4
  for $i=6 to $colsDiffUsed
   $data3 = _ExcelReadCell($openDiff, $x, $i)
   if not $data3 = "" Then
    if _ExcelReadCell($newDiff, $x, $changeCell) = "" Then
     _ExcelWriteCell($newDiff, _ExcelReadCell($openDiff, 1, $i) & $data3, $x, $changeCell)
    Else
     $changeCell = $changeCell + 1
     _ExcelWriteCell($newDiff, _ExcelReadCell($openDiff, 1, $i) & $data3, $x, $changeCell)
    EndIf
   EndIf
Next
Next

Edited April 27, 2012 by boyd108

[boyd108]

The top portion that iterates through retrieving $data1 and $data2 is still incredibly slow. That is where I need to most help changing my code.

for $x=2 to $rowsDiffUsed
  $guidNum = _ExcelReadCell($openDiff, $x, 1)
   $data1 = $openSnapshot.Application.WorksheetFunction.VLOOKUP( $guidNum, $openSnapshot.Range("A1:G80000"), 7, FALSE )
   $data2 = $openSnapshot.Application.WorksheetFunction.VLOOKUP( $guidNum, $openSnapshot.Range("A1:G80000"), 6, FALSE )
  _ExcelWriteCell($newDiff, $data1, $x, 2) ;Item Name
  _ExcelWriteCell($newDiff, $data2, $x, 3) ;Item ID
Next

[Beege]

He a few tips that would help the performance some. (nothing crazy though)

$changeCell = 4
For $i = 6 To $colsDiffUsed
    $data3 = _ExcelReadCell($openDiff, $x, $i)
    If $data3 Then
        If Not _ExcelReadCell($newDiff, $x, $changeCell) Then
            _ExcelWriteCell($newDiff, _ExcelReadCell($openDiff, 1, $i) & $data3, $x, $changeCell)
        Else
            $changeCell += 1
            _ExcelWriteCell($newDiff, _ExcelReadCell($openDiff, 1, $i) & $data3, $x, $changeCell)
        EndIf
    EndIf
Next

Have you tried using _ExcelReadSheetToArray(). I would run a quick run with that function and view the data with _Arraydisplay(). It will give you an idea of how long it should take to read all the values in the sheet. Its much faster to read in all the data at once, then modify the data in your array basically the same way your doing it now. Then when your array is all done write it all back to the excel sheet with _ExcelWriteSheetFromArray().