How can i add VBA code to the following

January 1, 2015 at 14:49:46
Specs: Windows 7
The coding hereunder gives me the names of the winners who select all 9 results each round in a competition or jackpots each round if there are no winners. I would like to include code for the final round in the competiton, (results of which are recorded in cell range AH2:AH151)as follows. If cell range AH2:AH151 >0 then ("winners" of the final round) else (No winners for the final round)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet1.Range("I155") = 9 Then
With Range("AI2:AI151")
Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
If Not num Is Nothing Then
firstAddress = num.Address
Do
tempnames = tempnames & Range("C" & num.Row) & "; "
Set num = .FindNext(num)
Loop While Not num Is Nothing And num.Address <> firstAddress
End If
End With
If tempnames <> "" Then
Range("P155") = "- " & Left(tempnames, Len(tempnames) - 2) & " Won The Jackpot For This Round. Jackpot Next Week $15"
Else: Range("P155") = "- No Jackpot Winners For This Round, Jackpot Next Week $" & (Range("O155") + 15)
End If
End If
End Sub

See More: How can i add VBA code to the following

Report •


#1
January 1, 2015 at 14:53:19
The coding hereunder gives me the names of the winners who select all 9 results each round in a competition or jackpots each round if there are no winners. I would like to include code for the final round in the competiton, (results of which are recorded in cell range AH2:AH151)as follows. If cell range AH2:AH151 >0 then ("winners" of the final round) else (No winners for the final round)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet1.Range("I155") = 9 Then
With Range("AI2:AI151")
Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
If Not num Is Nothing Then
firstAddress = num.Address
Do 
tempnames = tempnames & Range("C" & num.Row) & "; "
Set num = .FindNext(num)
Loop While Not num Is Nothing And num.Address <> firstAddress
End If
End With
If tempnames <> "" Then
Range("P155") = "- " & Left(tempnames, Len(tempnames) - 2) & " Won The Jackpot For This Round. Jackpot Next Week $15"
Else: Range("P155") = "- No Jackpot Winners For This Round, Jackpot Next Week $" & (Range("O155") + 15)
End If
End If
End Sub


Report •

#2
January 2, 2015 at 20:23:10
This is untested because I don't have access to Excel at the moment.

You didn't say where you wanted the result, so I used P156

 For rw = 2 To 151
  If Range(”AH“ & rw) > 0 Then
    tempFinal = tempFinal & Range("C" & rw) & "; "
  End If
 Next
   If tempFinal <> "" Then
     Range("P156") = "- " & Left(tempFinal, Len(tempFinal) - 2) & " Won The Final Round"
   Else: Range("P156") = "- You Are All Just A Bunch Of Losers"
   End if
      

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
January 3, 2015 at 08:21:53
No it is not working but i'm not sure where to place it within the existing code.
The Result is to go into Cell P 155

Report •

Related Solutions

#4
January 3, 2015 at 11:30:08
When you call your mechanic do you tell him "My car's not working" or do you tell him what it is/isn't doing? Since we can't see your workbook from where we're sitting, it would help if you told us what "it is not working" means. Is it throwing out errors? Is it not returning the results you wanted? Is it crashing your computer? etc.

I notice that the quotes around ”AH“ don't look right. If the code is throwing out an error, you might want to delete those quotes in the VBA editor and retype those quotes.

As far as where you want to put the code, that would all depend on when you want that particular section of code to run. I notice that the original code is placing the result for the current "Round" in P155 , yet you say the result for this new piece of code should also be placed in P155.

That's a little confusing to me, but the answer to "I'm not sure where to place it within the existing code" will depend on which answer you want in P155 after the code is run: The result based on the values in Range("AI2:AI151") or the result based the values in Range("AH2:AH151")...or both.

BTW...Have I suggested this tutorial before?

http://www.computing.net/howtos/sho...

You really should review it if you haven't already. It might save you some time by helping you debug/write your own code. We don't mind helping, but sometimes we are not available when you need us. The more you are able to learn on your own, the better you'll be at helping yourself. I learned a lot about writing VBA code by using the techniques described in that tutorial to help me understand how code written by others was working. The Single Stepping technique and the Watch window are extremely powerful. It is amazed what you can learn when you can watch VBA Code run one step at a time and watch what each variable is being set to at each step.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question