Looping : For... Next problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I modified the following code to loop thru rows 2 to 5.
The strInitial won’t reset to take the value of the next row, It’ll remain
showing the value of the first row for as many times there are in N, all the
other variables reset to the next N.
Any suggestions?

For N = 2 To 5 'rows
For i = 0 To UBound(myArray)

If myArray(i) > 0 Then

'Concatenate array values with column values to create a range for the
replacement string
strToReplace = "[" & myArray(i) & "]" 'Create string to be replaced from
array value
strRange = myArray(i) & N 'column and row
strReplacement = Range(strRange) 'Create the replacement string
'Replace existing string with replacment string
strInitial = Replace(strInitial, strToReplace, strReplacement, 1, 1) 'not
looping thru row
Else
i = UBound(myArray)
End If
Next i
MsgBox strInitial 'MsgBox displays the new string
Next N
 
It looks like your code starts off with strInitial having some initial
value; however you are changing the value inside the loop, so the next time
through the loop, it has the last value it was changed to. I would suggest
you create a new variable to do the modifications to, and reassign the
initial value on each loop. Here is what I am thinking.

For N = 2 To 5
CopyOfInitial = strInitial
For i = 0 To UBound(myArray)
If myArray(i) > 0 Then
strToReplace = "[" & myArray(i) & "]"
strRange = myArray(i) & N
strReplacement = Range(strRange)
CopyOfInitial = Replace(CopyOfInitial, strToReplace, strReplacement,
1, 1)
Else
i = UBound(myArray)
End If
Next i
MsgBox CopyOfInitial
Next N

Here I created a variable named CopyOfInitial to modify within the inner
loop and which I reset at the beginning of the outer loop. That way, the
contents of strInitial are never destroyed and can be recalled at anytime.

Rick
 
Back
Top