SELECT CASE change CASE after SELECT?

[Clif McIrvin]

How would I re-enter a SELECT CASE because a condition required changing
the CASE value? Would a GOTO to re-enter the SELECT from inside the
SELECT CASE cause unreleased stack space or otherwise confuse VBA?

I happen to be working in Excel but posted here because this seems to be
a VBA question rather than application specific.

Begin Code Fragment::::::::

(new restart label here)
Set xRow = ActiveCell.EntireRow
With xRow
Select Case Left(.Cells(1), 3) 'Plant
Case "LCP"
MsgBox "Please Select MCP Lot", vbCritical, _
"Unable to extract MCP lot#"
Exit Function
Case "MCP"
(other Case clauses)
End Select
End With
Set xRow = Nothing

End Fragment:::::::::::::

In the case of the data in question, "LCP" is a 'rare' value, and 'MCP'
is quite likely nearby. I'm thinking of changing the msgbox / exit
function to code that would walk the data until it found the nearest
"MCP" value, reset the active cell and goto (new restart label here).

Suggestions, please?

 

[krazymike]

This is pretty easy. Just put a label above the select statement. I
wrote just a little thing to demo this. Try this:

sub a()
dim str as String
str = "hi"
THIS_IS_THE_LABEL:
select case str
case "duh"
debug.print str
case "hi"
debug.print str
str="duh"
goto THIS_IS_THE_LABEL
end select
debug.print "DONE!"
end sub

 

[Clif McIrvin]

Thanks -- I was pretty sure it'd work; I was hoping for someone to jump
in and address whether it could lead to a memory leak or stack overflow
or some such.

Haven't gotten away from my paycheck work long enough to actually try it
yet, tho.

Thanks again.

--
Clif

This is pretty easy. Just put a label above the select statement. I
wrote just a little thing to demo this. Try this:

sub a()
dim str as String
str = "hi"
THIS_IS_THE_LABEL:
select case str
case "duh"
debug.print str
case "hi"
debug.print str
str="duh"
goto THIS_IS_THE_LABEL
end select
debug.print "DONE!"
end sub