Thank you all.

[Trueblue862]

Hi, over the past 12 months or so I have been slowly teaching myself to code, using this site as my lifting off point. I have to say thank you for all the help I have received and I'm sure I have frustrated more than a few of the members here, and probably will more into the future, for this I apologise. Anyway, last week I heard of the fizzbuzz test for the first time, and I had a bit of time on my hands today so I thought I would give it a crack. It took me a little head scratching but I eventually got it. The main part I had trouble with was evaluating whole numbers, once I had that down it worked perfectly, mind you it took more time than I care to admit. Although I fully understand there are many ways to attack this problem, and I know mine isn't the best, I would like to hear from some of the gurus here as to what their opinions are of my code and ways I could go about improving further. Thank you to all the excellent contributors to this forum for getting me this far.

Sub fizzBUZZ()
Dim cl As Range
Dim x As Variant
Dim y As Variant

 i = 1
Set cl = Sheets("Sheet1").Range("A1:A100")
For Each cl In cl
x = i / 3
y = i / 5

    If x = Int(x) And y = Int(y) Then
        cl.Value = "FIZZBUZZ"
    ElseIf x = Int(x) Then
        cl.Value = "FIZZ"
    ElseIf y = Int(y) Then
        cl.Value = "BUZZ"
    ElseIf x <> Int(x) And y <> Int(y) Then
        cl.Value = i
    End If
    
  i = i + 1
 If i > 100 Then GoTo thr
 Next
thr:
End Sub

 

[jasonb75]

One thing to remember is that if it works it is fine. Many people will try to tweak their code to make it more efficient, often more out of obsession than necessity.

The best method is the one that works and that you understand.

The only things that I have noticed in your code which (in my opinion) could be improved are the variable declarations.

i has not been declared at all. x and y would be better declared as double rather than variant.
While there is nothing wrong with re-using variables, using the same one for 2 things at the same time (the cl variable) could be a recipe for disaster. For Each c In cl would be safer, or something like For Each cl In Rng would be more common.

I've added my take on the task below to give you something to look over and compare. There are likely many different ways of achieving this that we wouldn't think of.
I did have a quick go at doing it with a dynamic array formula but that did not go well ?

Sub fizzBUZZ()
Dim c As Range, rng As Range
Dim x As Long, y As Long, i As Long
x = 3: y = 5: i = 1
Set rng = Sheets("Sheet1").Range("A1:A100")
For Each c In rng

    If i Mod x = 0 Then
        c.Value = IIf(i Mod y = 0, "FIZZBUZZ", "FIZZ")
    ElseIf i Mod y = 0 Then
        c.Value = "BUZZ"
    Else
        c.Value = i
    End If
    
  i = i + 1
 If i > 100 Then Exit For
 Next
End Sub