Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING BUT THE MARKS ARE NOT IN SEQUENTIAL ORDER #2
hello Tom Ogilvy ( you responded to this earlier, but i still need help
with it)or any one that could help me fix the 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 which they all start at cell A3 in sheetQDS. the number of marks =Nu, as you can see i am looping from 1 to Nu. but the problem with this code, 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 or wheteher it is a J1 OR J1S? i just need it so that after it process the mark to know what is the next different mark to prcess.Thanks for any assitance! '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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING BUT THE MARKS ARE NOT IN SEQUENTIAL ORDER #2
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 -- Regards, Tom Ogilvy wrote in message oups.com... hello Tom Ogilvy ( you responded to this earlier, but i still need help with it)or any one that could help me fix the 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 which they all start at cell A3 in sheetQDS. the number of marks =Nu, as you can see i am looping from 1 to Nu. but the problem with this code, 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 or wheteher it is a J1 OR J1S? i just need it so that after it process the mark to know what is the next different mark to prcess.Thanks for any assitance! '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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advancing numbers in sequential order | Excel Discussion (Misc queries) | |||
LOOPING BUT THE MARKS ARE NOT IN SEQUENTIAL ORDER | Excel Programming | |||
Countif data is not all in sequential order | Excel Discussion (Misc queries) | |||
data from one sheet to several in sequential order! | New Users to Excel | |||
Sequential looping | Excel Programming |