Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent,! Need help with this Macro
Hi All:
i would like your help with this macro. I have created a userform, which i named "JOIST",with two options. First option the user could select "ALL" to design all marks(J1,J2,J3,..........) which are shown in column A. when some one selects the option "ALL" on the userform, it puts in the word "ALL" in shtWOOD.Cells(1, 2) which is cell"B2". I have added an "if statement" If shtWOOD.Cells(1, 2).Value = "ALL" Then Nu = shtQDS.Range("BC1").Value For some reason, despite cell B2 is showing the word "ALL",it does not execute(SKIPS) the next statement after "Then" which is Nu = shtQDS.Range("BC1").Value, where Nu is the number of all marks(J1,J2,J3,....) that exist in column A. how can i correct the code so that when some one select" ALL" in the userform, to tell it that" Nu" is equal to the value Nu = shtQDS.Range("BC1").Value Second Option is for the user to "Select Mark", where he would type the mark in the textbox next to that option, and then the program will analyz that specific mark. The marks can be (J1,J2,J3.....................**.). that optin is not incorporated in the code which i also would like help on. Right now the program will analyze all the marks in that column. i just would like to make it more flexible. Ththe name of the userform is "JOIST". I apprecaite any help! This is part of the code : Set shtQDS = ThisWorkbook.Sheets("QDS") Set shtWOOD = ThisWorkbook.Sheets("Wood Info") Set shtEM = ThisWorkbook.Sheets("Existing Material") Set shtPA = ThisWorkbook.Sheets("Panel Analysis") Set shtNCS = ThisWorkbook.Sheets("NEW CHORD SIZES") Set shtAP = ThisWorkbook.Sheets("Angle Properties") Set shtResults = ThisWorkbook.Sheets("Results") Set shtNPCJ = ThisWorkbook.Sheets("New Partial Composite Joist") 'Get the mark 'Nu = Int(Application.InputBox("Numb*er of Joist marks?")) JOIST.Show If shtWOOD.Cells(1, 2).Value = "ALL" Then Nu = shtQDS.Range("BC1").Value For Z = 1 To Nu Step 1 strMark = "J" & Z End If 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").clearcont*ents shtEM.Range("B10:C13").clearco*ntents shtPA.Range("B33:AO44").clearc*ontents shtPA.Range("B66:AO76").clearc*ontents 'shtNCS.Range("A3:H1000").clea*rcontents shtResults.Range("A3:AR65536")*.clearcontents shtNPCJ.Range("B10:C13").clear*contents intResultRow = 3 'Set initial values i = 3 intLines = 0 intLoadCase = 1 boolCancel = False Application.ScreenUpdating = False Worksheets.Add after:=Worksheets(Worksheets.C*ount) NewSheet = ActiveSheet.Name With Worksheets(NewSheet) .Move after:=Worksheets(Worksheets.C*ount) .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 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("NEW CHORD SIZES").Cells(R, 1).Value = strMark Sheets("NEW CHORD SIZES").Cells(R, 2).Value = intQty Sheets("NEW CHORD SIZES").Cells(R, 3).Value = dblLength Sheets("NEW CHORD SIZES").Cells(R, 4).Value = intETCA Sheets("NEW CHORD SIZES").Cells(R, 5).Value = intEBCA Sheets("NEW CHORD SIZES").Cells(R, 6).Value = Sheets(strMark).Range("D2").Va*lue Sheets("NEW CHORD SIZES").Cells(R, 7).Value = Sheets(strMark).Range("E2").Va*lue Exit For Next Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent,! Need help with this Macro
" wrote:
Hi All: i would like your help with this macro. I have created a userform, which i named "JOIST",with two options. First option the user could select "ALL" to design all marks(J1,J2,J3,..........) which are shown in column A. when some one selects the option "ALL" on the userform, it puts in the word "ALL" in shtWOOD.Cells(1, 2) which is cell"B2". I have added an "if statement" If shtWOOD.Cells(1, 2).Value = "ALL" Then Nu = shtQDS.Range("BC1").Value For some reason, despite cell B2 is showing the word "ALL",it does not execute(SKIPS) the next statement after "Then" which is Nu = shtQDS.Range("BC1").Value, where Nu is the number of all marks(J1,J2,J3,....) that exist in column A. how can i correct the code so that when some one select" ALL" in the userform, to tell it that" Nu" is equal to the value Nu = shtQDS.Range("BC1").Value Second Option is for the user to "Select Mark", where he would type the mark in the textbox next to that option, and then the program will analyz that specific mark. The marks can be (J1,J2,J3.....................Â*Â*.). that optin is not incorporated in the code which i also would like help on. Right now the program will analyze all the marks in that column. i just would like to make it more flexible. Ththe name of the userform is "JOIST". I apprecaite any help! This is part of the code : Set shtQDS = ThisWorkbook.Sheets("QDS") Set shtWOOD = ThisWorkbook.Sheets("Wood Info") Set shtEM = ThisWorkbook.Sheets("Existing Material") Set shtPA = ThisWorkbook.Sheets("Panel Analysis") Set shtNCS = ThisWorkbook.Sheets("NEW CHORD SIZES") Set shtAP = ThisWorkbook.Sheets("Angle Properties") Set shtResults = ThisWorkbook.Sheets("Results") Set shtNPCJ = ThisWorkbook.Sheets("New Partial Composite Joist") 'Get the mark 'Nu = Int(Application.InputBox("NumbÂ*er of Joist marks?")) JOIST.Show If shtWOOD.Cells(1, 2).Value = "ALL" Then Nu = shtQDS.Range("BC1").Value For Z = 1 To Nu Step 1 strMark = "J" & Z End If Application.ScreenUpdating = False 'Error check 'If strMark = "" Then ' MsgBox "No mark chosen. Analysis cancelled." 'GoTo CancelAnalysis 'End If ------snip ----------- It's been a long time since I've done any programming in Excel. Had to put on the thinking cap again. In your code, the IF() statement is evaluating to FALSE because "ALL" < "all" < " All" < "aLL" The easiest thing to do is force one side to be all lower case or all upper case. I've always used the UCase() function because it stands out when reading the code. ---begin snippet---- If UCase(shtWOOD.Cells(1, 2)) = "ALL" Then ' the default property is "Value" so don't need to explicitly write it Nu = shtQDS.Range("BC1") '?????? this loop doesn't do anything For Z = 1 To Nu strMark = "J" & Z Next 'added ' this will give the same result ' strMark = "J" & NU End If ---end snippet---- Also, in the code you posted, I noticed there were several places that had missing lines ("Next", "End If") and there was a place where the code jumped out of a Do loop using a GOTO, then re-entered the loop. This will probably crash the code. As for your second question, is there a combo box that you use to select a "mark" that then sets the focus to an associated text box? Or do you want the code to loop thru, skipping empty text boxes and analyzing the rest of the marks? Without seeing all the code, it is difficult to know what to suggest. HTH --- SteveS -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO URGENT HELP | Excel Discussion (Misc queries) | |||
Urgent - Help VBA Macro | Excel Discussion (Misc queries) | |||
Urgent Macro help needed ! | Excel Programming | |||
Macro help urgent urgent | Excel Programming | |||
Macro help urgent urgent | Excel Programming |