Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

VB routine required

I have a list of data where there are a's in the second column

- Whenever there is a single a series I want to run routine x with a single argument sending the row number
- Whenever there is a continuous series of a's I want to run routine y with two arguments sending the first and last row numbers

so for the list

1
2          a
3
4          a
5          a
6          a
7          a
8
9
10
11          a
12          a
13          a
14
15
16
17          a
18
19
20

I would send

call x(2)
call y(4,7)
call y(11,13)
call x(17)

can someone write me a routine to do this?
lets say the numbers are stored in c(1 to 20,1)
and the a's are stored in c(1 to 20,2)

Thanks

Saqib
0
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 5
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
Public Sub Routine()
    Dim Row As Long
    For Row = 1 To 20
        If Len(Cells(Row, 2)) = 0 Then
            X Cells(Row, 1)
        Else
            Y Cells(Row, 1), Cells(Row, 2)
        End If
    Next Row
End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Ignore my post...misread the question.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Public Sub Routine()
    Dim Row As Long
    Dim LastBlankRow As Long
    For Row = 1 To 20
        If Len(Cells(Row, 2)) = 0 Then
            LastBlankRow = Row
        Else
            If Len(Cells(Row + 1, 2)) = 0 Then
                If Row - LastBlankRow = 1 Then
                    X Cells(Row, 1)
                Else
                    Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
                End If
            End If
        End If
    Next Row
End Sub

Kevin
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Saqib Husain, SyedEngineerAuthor Commented:
Great. Thanks.

Saved me the effort.

Saqib
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Hi kevin,

can you modify this to not look beyond row 20? I dont have a 21st row
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Hi kevin,

Can you modify this so that it does not look beyond row 20?

I dont have a row 21 and if there is an a in row 20 then the program crashes

Saqib
0
 
Saqib Husain, SyedEngineerAuthor Commented:
I have opened a new question for this
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now