Urgent,! Need help with this Macro

[sam.fares]

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

 

[SteveS]

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