FOR NEXT Problems .....

[Blobbies]

Hi there!

I really do just bumble my way through this stuff, so I apologise for my
lack of technical know-how!

I want a procedure (set within a "for .... next") that says: if (a happens)
and (b happens) and (c happens) then "Name" & s = (a number, i.e. 5 or 6 or 7
etc)

I'm trying to do it like this so I really don't have to post my actual code!
I think the problem lies in the ("Name" & s) area!? It will work and give
simply "Name" a value, but I need values for Name1, Name2, Name3 etc

Hopefully you guys can help without me having to embarrass myself with
posting code!?



Mike

 

[anon]

This may help but it's difficult without your code to understand
better.

In the example below the code checks the values in column a, b and c
plus the row (defined by a) are equal to 1. If so column d and the row
are given the value "Name" & s.

Sub test()
Dim a As Integer
Dim s As Integer
s = 1
For a = 1 To 10
If ActiveSheet.Range("a" & a).Value = 1 Then
If ActiveSheet.Range("b" & a).Value = 1 Then
If ActiveSheet.Range("c" & a).Value = 1 Then
ActiveSheet.Range("d" & a).Value = "Name" & s
s = s + 1
Else
'do nothing
End If
End If
End If
Next a
End Sub

 

[Don Guillett]

for i = 1 to 21
if cells(i,1)=1 and cells(i,2)="b" and cells(i,3)*cells(i,4)=45 then msgbox
"Hi there"
next i

OR use SELECT CASE

 

[Blobbies]

Hi there! Thanks for taking the time to help out!

Here's the line of code that I'd like to make work, if possible!!

If "MP" & s = 5 And Shots = 0 And ("cbxHole" & s - "MP" & s = -4) Then
"Stable"&s = 6

It gives a Compile error - the last bit!

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

What are Name1, Name2, etc.... variables or control names? If control names,
what kind and where are they located... the worksheet or a UserForm?

Rick

 

[Sandy Mann]

What are:

"MP", "cbxHole" & "Stable" Named cells?

I assume that s & Shots are variables.

If the above is the case then the line can be re-written:

If Range("MP").Value + s = 5 And Shots = 0 _
And (Range("cbxHole").Value + s) - (Range("MP").Value + s) = -4 _
Then Range("Stable") = 6 - s

But as we are adding s to both Range("cbxHole").value and Range("MP").Value,
we can simply omit them to give:

If Range("MP") + s = 5 And Shots = 0 _
And (Range("cbxHole").Value) - (Range("MP").Value) = -4 _
Then Range("Stable") = 6 - s


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Blobbies]

Hi Rick

Thanks for your time! Right - I have bitten the bullet and posted all the
code - I know that it will be very raw and entirely without order and won't
follow rules etc, but I can usually bumble my way through!

It is a procedure to work out stableford points in golf!

See what you think!

Private Sub CommandButton5_Click()
Application.ScreenUpdating = False
Worksheets("Players").Select
Worksheets("Players").Unprotect
Range("A1").Select
With ActiveCell.EntireColumn
.Find(cbxPlayerName).Select
End With
Dim Shots As Integer


Set CourseHandicap = Cells(ActiveCell.Row, ActiveCell.Column + 4)
Set ScoringSex = Cells(ActiveCell.Row, ActiveCell.Column + 1)

For s = 1 To 18
If ScoringSex = "M" Then GoTo MEN
If ScoringSex = "F" Then GoTo WOMEN

MEN:
If (CourseHandicap - "MS" & s) <= 0 Then Shots = 0
If (CourseHandicap - "MS" & s) >= 1 And (CourseHandicap - 10) <= 18 Then
Shots = 1
If (CourseHandicap - "MS" & s) > 18 And (CourseHandicap - 10) <= 36 Then
Shots = 2
If (CourseHandicap - "MS" & s) > 36 Then Shots = 3

If ScoringSex = M Then GoTo MISSTHEWOMEN

WOMEN:

If (CourseHandicap - "WS" & s) <= 0 Then Shots = 0
If (CourseHandicap - "WS" & s) >= 1 And (CourseHandicap - 10) <= 18 Then
Shots = 1
If (CourseHandicap - "WS" & s) > 18 And (CourseHandicap - 10) <= 36 Then
Shots = 2
If (CourseHandicap - "WS" & s) > 36 Then Shots = 3

MISSTHEWOMEN:
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = -4 Then
"Stable"&s = 6
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = -3 Then
"Stable"&s = 5
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = -2 Then
"Stable"&s = 4
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = -1 Then
"Stable"&s = 3
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = 0 Then
"Stable"&s = 2
If "MP" & s = 5 And Shots = 0 And "cbxHole" & s - "MP" & s = 1 Then
"Stable"&s = 1

Next s

End Sub


This procedure strikes problems at the "Then "Stable"&s =" part!

MP(Men's Par or WP women's par) is the par score for the holes (1-18) and MS
or WS is the Men's/Women's stroke hole figure, which are both required to
work out the stableford points.
The course handicap comes from adetails sheet and is particular to each player
MP/WP are assigned when the (enter the scores) userform is initiated, as are
the MS/WS.
cbxHole1 - cbxHole18 are entered into the userform, depending on the
player's scores for the hole!



Thanks for your efforts!!

 

[Don Guillett]

Without seeing your project I see several areas of improvement.

If ScoringSex = M Then GoTo MISSTHEWOMEN

If ucase(ScoringSex) = "M" Then GoTo MISSTHEWOMEN

Also you do not have to goto the players sheet or even unprotect it to work
with it

Sub golf2()
What = "ddd"
With Worksheets("Players")
Set c = .Columns(1).Find(What, LookIn:=xlValues, After:=.Cells(1, 1), _
lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not c Is Nothing Then

Set CourseHandicap = c.Offset(, 4)
Set ScoringSex = c.Offset(, 1)
If UCase(ScoringSex) = "M" Then MsgBox "oK"
'etc

MsgBox CourseHandicap
MsgBox ScoringSex
Else

 

[Rick Rothstein \(MVP - VB\)]

Okay, you didn't answer my "are the variables or are the controls" question
directly, but it looks like they are controls. You can reference a control
on a UserForm using the Me.Controls collection. For example, assuming MS is
a control where a value is typed or selected, this line which you
attempted...

If (CourseHandicap - "MS" & s) <= 0 Then Shots = 0

would be written this way...

If (CoursHandicap - Me.Controls("MS" & s).Value) <= 0 Then Shots = 0

So, for controls, where you are attempting to write this...

ControlName & Variable

use this instead....

Me.Controls(ControlName & Variable)

and I think you should always specify a property rather than relying on the
default property to be supplied by VB when you omit specifying one.

Rick

 

[Blobbies]

Hi Rick

Thank you so much for your help! It's always something simple, but I just
don't have that depth of knowledge!! However, I do now have a new piece of
info' to tuck away!!

Thank you!



Mike