Looping

[Linda21]

Hello all,
My code is correctly doing the following:

1. Run routine MatchFundName and loop until a match is found - the key info is that RowSD should be '13' throughout this loop - a different variable will increase by 1 during this loop
2. When match is found, call routine MatchEntity. This routine also needs RowSD to be '13' throughout this loop. This subroutine loops until a different match is found and data should be updated.
3. The cycle is complete.
4. Now I want to start all over again with MatchFundName but now RowSD needs to be '14'.
5. Both routines are public.
6. Can I call MatchEntity from MatchFundName and back and forth as many times as I want?
7. How do I get RowSD to change to 14, then 15, etc.
8. I think I need a new variable somewhere, but am struggling with where it should go.
9. How do I declare RowSD as 13 on a global level? Maybe that would work?

Thank you to anyone who has the patience to read through and understand what I am attempting!

 

[craig.penny]

Can you post some of the code?

 

[Linda21]

I am starting with MatchFundName first...

Public Sub MatchFundName() 'find match of fund name on Implementation and SD_
FundColImp = 30
RowImp = 9
FundColSD = 2
RowSD = 13
FundImp = Implementation.Cells(RowImp, FundColImp)
FundSD = SD_.Cells(RowSD, FundColSD)
MsgBox (Match)

Do Until FundSD = FundImp
FundImp = Implementation.Cells(RowImp, FundColImp)
FundSD = SD_.Cells(RowSD, FundColSD)
RowImp = RowImp + 1 ' continue looking through Implementation - Fund Name column to match

If FundSD = FundImp Then ActiveSDRow = RowSD ' assign active SD_row to variable for sub MatchEntity
If FundSD = FundImp Then ActiveImpRow = RowImp - 1 ' assign active Implementation row to variable for sub MatchEntity
If FundSD = FundImp Then Call MatchEntity ' call routine to match the entity column

Loop

End Sub


Public Sub MatchEntity()
EntityColSD = 23
EntityColImp = 31
EntityRow = 6
PortfolioSD = 1
EntitySD = SD_.Cells(ActiveSDRow, EntityColSD)
EntityImp = Implementation.Cells(EntityRow, EntityColImp)
Do Until EntitySD = EntityImp
EntitySD = SD_.Cells(ActiveSDRow, EntityColSD)
EntityImp = Implementation.Cells(EntityRow, EntityColImp)

If EntitySD = EntityImp Then MsgBox ("entity match")
If EntitySD = EntityImp Then PortfolioPostCol = EntityColImp
If EntitySD = EntityImp Then Exit Do

' add code to stop entity + 4
EntityColImp = EntityColImp + 4
Loop
If EntitySD = EntityImp Then

Implementation.Cells(ActiveImpRow, PortfolioPostCol) = SD_.Cells(ActiveSDRow, PortfolioSD)

Match = True
MsgBox (RowSD & " rowsd after update portfolio and before call routine")
If RowSD < 300 Then Call MatchFundName

End If


End Sub

 

[craig.penny]

Try replacing

RowSD = 13

With

Static RowSD As Integer
If RowSD = 0 Then
      RowSD = 13
Else
      RowSD = RowSD + 1
End If

Every time this sub is called it will increment RowSD. You can put the same few lines in your other sub and it will do the same thing.

The other way to handle this is to declare a global variable as you mentioned. Programmers who are many times better than me often recommend against it so I personally try my best to avoid it.