| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FOR NEXT Problems .....
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| aauugghhh...#div/o problems & various average formula problems | Excel Worksheet Functions | |||
| More zip + 4 problems | Excel Discussion (Misc queries) | |||
| Problems with MAX | Excel Worksheet Functions | |||
| Tab key problems | Excel Discussion (Misc queries) | |||
| Problems merging an excel file due to code or file problems? | Excel Programming | |||
Hybrid Mode
