Finding a matching cell to a string, then setting a new range by using count

[cencinaNB2ET]

I want to do the following:

1. To read a cell in an excel file by finding the matching cell in one column

2. From there ( count) current row, set another search to find the next empty cell on an adjacent column

3. extract the array of cells found in those cells..

I can do 1 and 3 but i cant count function twice!!

 SyntaxEditor Code Snippet

Dim Reference_File As String
Reference_File = RuleArguments("Reference_File")

'['Populate the Parameter from Excel File
GoExcel.Open(Reference_File, "S Series" )
' Define Range
InitialRowStart = 1
InitialRowEnd = 200
For count = InitialRowStart To InitialRowEnd
' If it's blank count it
If (GoExcel.CellValue("A" & count)) = ThisFileName Then
Heading_Row_A = count
'MessageBox.Show(Current_Row_A, "Title")
End If
Next


'Make SUre that 40 is a number that adds to the current first row search, not too big not too small, ' As long as it covers all possibilities of finding a pipe
FirstRangeRow = Heading_Row_A
EndRangeRow = FirstRangeRow + 25


For count = FirstRangeRow To EndRangeRow
' If it's blank count it
If String.IsNullOrEmpty(GoExcel.CellValue("B" & count)) Then 
i = i + 1
End If
Next

' Next empty row is max rows minus blank row
LastRow = EndRangeRow - i + 1
FirstRow = FirstRangeRow 
']



' We get the lastRow and the firstrow to create arrays and search conditions'check if its correct
MessageBox.Show(FirstRow, "First Row Number")
MessageBox.Show(LastRow, "Last Row Number - Empty")

[cencinaNB2ET · Accepted solution]

It was actually easy...

Just had to set   2 for loops without the the count function... and start adding numbers from the top rather than the bottom like it was suggested in a blog previously.. 

Here is the Schematic of the code. Pretty simple

SyntaxEditor Code Snippet

GoExcel.Open(Excel_Reference_File, Worksheet )
Dim Row As Integer
Dim StartRow1 As Integer = 1
Dim EndRow1 As Integer = 100
Dim Row1 As Integer
Dim Row2 As Integer
Dim n As Integer

For Row1 = StartRow1 To EndRow1
If(GoExcel.CellValue("A" & Row1)) = ThisFileName Then
'MessageBox.Show("Found Pipes on Row:" & Row1 , "Row Found")
Exit For
End If
Next


Dim StartRow2 As Integer = Row1 + 1
Dim EndRow2 As Integer = StartRow2 + 50
'MessageBox.Show("Start Range : " & StartRow2 & vbCr & "End Range : " & EndRow2  , "Test")
For n = StartRow2 To EndRow2
If String.IsNullOrEmpty(GoExcel.CellValue("B" & n)) Then
Row2 = n - 1
'MessageBox.Show("End of Range is : " &  Row2 , "Test")
Exit For
End If
Next