LOOPING BUT THE MARKS ARE NOT IN SEQUENTIAL ORDER #3

[sam.fares]

hello :
i appreciate any help to modify this procedure. you will find
my original question under "looping but the marks are not in sequential

order" Thanks alot!

Attached is my code. as you can see i am determining the number of J
marks in coulmn.Marks start at cell A3 in sheetQDS. the number
of different marks =Nu, as you can see I am looping from 1 to Nu. but
the problem
with this procedure is that, it works fine if all my marks are
J1,....J2....J3 so
that there is no gaps in the numbering system and the number after the
J is an integer. it does not work if i have J1A, OR J5C. how could i
fix it to do that regrdless whether there is a gap or not in the
numbering or wheteher
it is a J1 OR J1S? it needs to recognize the name of the next mark to
be process. right now it increments by one. so i might have 5 different
marks, lets say J1, J4, J7A, J6, J9C. It reconizes there are 5
different marks in column starting at A3, but when it goes through the
loop and process each mark, it thinks the marks are numbered like this
J1,J2,J3,J4,J5 which messes up the results. .Thanks for anyassitance!

see my code below.

'get the number of marks
lastrow = shtQDS.Range("A3").End(xlDown).Row
Set rng = shtQDS.Range("A3:A" & lastrow)
v = rng
On Error Resume Next
For M = LBound(v) To UBound(v)
nodupes.Add v(M, 1), CStr(v(M, 1))
Next
On Error GoTo 0
numUnique = nodupes.Count
If shtWOOD.Cells(1, 2).Value = "ALL" Then
'Nu = shtQDS.Range("BC1").Value
Nu = numUnique
End If
For Z = 1 To Nu Step 1
strMark = "J" & Z
Application.ScreenUpdating = False
'Error check
'If strMark = "" Then
' MsgBox "No mark chosen. Analysis cancelled."
'GoTo CancelAnalysis
'End If


'Clear old data
shtEM.Range("B2:B9").clearcontents
shtEM.Range("B10:C13").clearcontents
shtPA.Range("B33:AO44").clearcontents
shtPA.Range("B66:AO75").clearcontents
'shtSummary.Range("A3:H1000").clearcontents
shtResults.Range("A3:AR65536").clearcontents
shtNPCJ.Range("B10:C13").clearcontents
intResultRow = 3


'Set initial values
i = 3
intLines = 0
intLoadCase = 1
boolCancel = False


Application.ScreenUpdating = False
Worksheets.Add after:=Worksheets(Worksheets.Count)
NewSheet = ActiveSheet.Name
With Worksheets(NewSheet)
.Move after:=Worksheets(Worksheets.Count)
.Name = strMark
.Activate
Columns("A:A").ColumnWidth = 51.14
Columns("B:AR").Select
Selection.ColumnWidth = 13
ActiveWindow.Zoom = 75
End With


'Find out how many load cases we're dealing with
Mark:
Do
If shtQDS.Cells(i, 1).Value = strMark Then
If shtQDS.Cells(i, 4).Value > intLoadCases Then intLoadCases =
shtQDS.Cells(i, 4).Value
End If
i = i + 1
Loop Until IsEmpty(shtQDS.Cells(i, 1))


i = 3


FindNext:


'Find valid row
Do
If shtQDS.Cells(i, 1).Value = strMark And shtQDS.Cells(i, 4).Value
= intLoadCase Then
GoTo GetValues
Else
i = i + 1
End If


Loop Until IsEmpty(shtQDS.Cells(i, 1))


'After no more valid rows for that load case, run optimization routine
Optimize


'Determine if any more load cases exist
If intLoadCase < intLoadCases Then
intLoadCase = intLoadCase + 1
i = 3
GoTo FindNext
End If


For R = 2 + Z To Nu + 3 Step 1
Sheets("Summary").Cells(R, 1).Value = strMark
Sheets("Summary").Cells(R, 2).Value = intQty
Sheets("Summary").Cells(R, 3).Value = dblLength
Sheets("Summary").Cells(R, 4).Value = intETCA
Sheets("Summary").Cells(R, 5).Value = intEBCA
Sheets("Summary").Cells(R, 6).Value =
Sheets(strMark).Range("D2").Value
Sheets("Summary").Cells(R, 7).Value =
Sheets(strMark).Range("E2").Value


Exit For


Next
Next


Reply

 

[Tom Ogilvy]

The J Values are stored in Nodupes, so instead of building the J value by
doing

"J" & Z

just use the value in the collection

Instead of

For Z = 1 To Nu Step 1
strMark = "J" & Z

Dim itm as Variant

i = 0
for each itm in Nodupes
i = i + 1 ' in case you need to use i in your loop
strMark = itm


' code to process

Next itm