ExcelBanter - FOR NEXT Problems .....

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - FOR NEXT Problems ..... (https://www.excelbanter.com/excel-programming/416011-next-problems.html)

Blobbies

FOR NEXT Problems .....

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

FOR NEXT Problems .....

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 NEXT Problems .....

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Blobbies" wrote in message
...
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

Blobbies

FOR NEXT Problems .....

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!

"anon" wrote:

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

Rick Rothstein \(MVP - VB\)[_2644_]

FOR NEXT Problems .....

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

"Blobbies" wrote in message
...
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

Sandy Mann

FOR NEXT Problems .....

What a

"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

Replace @mailinator.com with @tiscali.co.uk

"Blobbies" wrote in message
...
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!

"anon" wrote:

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

Blobbies

FOR NEXT Problems .....

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!!

"Rick Rothstein (MVP - VB)" wrote:

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

"Blobbies" wrote in message
...
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

Don Guillett

FOR NEXT Problems .....

Rick Rothstein \(MVP - VB\)[_2649_]

FOR NEXT Problems .....

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" wrote in message
...
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!!

"Rick Rothstein (MVP - VB)" wrote:

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

"Blobbies" wrote in message
...
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

Blobbies

FOR NEXT Problems .....

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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!!

"Rick Rothstein (MVP - VB)" wrote:

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

"Blobbies" wrote in message
...
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

Mark Main

FOR NEXT Problems .....

You should provide more details because "Name" & s = (a number, i.e. 5 or 6
or 7 is confusing... I'm not exactly sure what you mean by that. Here's some
example code to work with. Hope it helps.

Sub MyNameNumbers()
'This will copy the first 20 cells from column A
'over to column B and also append the row number to it
For r = 1 To 20
Cells(r, 2) = Cells(r, 1) & " " & r
Next r
End Sub

"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Blobbies" wrote in message
...
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

---- Posted via Pronews.com - Premium Corporate Usenet News Provider ----
http://www.pronews.com offers corporate packages that have access to 100,000+ newsgroups

All times are GMT +1. The time now is 12:24 PM.