PDA

View Full Version : [SOLVED] Updating Win/Loss records with VBA Button



Bam_Mab91
09-09-2019, 02:03 PM
I am wanting to have my VBA compare to columns row by row. The row that has a greater value will have a 1 added and then print the result in a cell like a win - loss.
I only have one worksheet in this file. I am trying to compare cells B2:B18 to C2:C18.
This is what I have written so far:

Sub Record_Tracking()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim record As String
j = 0
k = 0
For i = 2 To 18
If [Worksheets(1).Cells(i,2).Value] > [Worksheets(1).Cells(i,3).Value] Then
j = j + 1
ElseIf [Worksheets(1).Cells(i,2).Value] < [Worksheets(1).Cells(i,3).Value] Then
k = k + 1
Else
j = j + 0
k = k + 0
End If
record = " - "
record = CStr(j) & record & CStr(k)
Next i


Worksheets(1).Range("B19").Value = "Record:"
Worksheets(1).Range("C19").Value = record
End Sub

When I click the button I have the macro on it does populate the cell but with 0-0. I am not used to super familiar with VBA, more with C and jython (used for ignition). Any tips would be great. I tried searching for topics and when through 3 to 4 pages before making the post because I didn't see what I needed. Sorry if this is a duplicate post.
Thanks in advance

Paul_Hossler
09-09-2019, 03:08 PM
Welcome to the forum - take a minute to read the FAQs in the link in my sig

1. I added CODE tags to your #1 to format. You can use the [#] icon to insert the opening and closing tags and then paste your macro between

2. I guessed at your data. Usually attaching a sample workbook helps

3. Also made some recommendations in the comments.

Personal Opinion: self documenting variable names are usually better. So 'iNumWins' instead of 'j', 'iNumLoses' instead of k.

This isn't FORTRAN :)



Option Explicit ' recommended (by me anyways)


Sub Record_Tracking()
Dim i As Long ' Long better that Integer
Dim j As Long
Dim k As Long

j = 0 ' VBA makes 0 at start, but I do this also
k = 0

With ActiveSheet ' assume the data is on the active sheet
For i = 2 To 18
If .Cells(i, 2).Value > .Cells(i, 3).Value Then
j = j + 1
ElseIf .Cells(i, 2).Value < .Cells(i, 3).Value Then
k = k + 1
End If
Next i


.Cells(19, 2).Value = "Record:" ' Range is OK, but I used .Cells to be consistent
'something like '12-3' will be formatted a Date possibly
'the leading single quote forces it to be a string
.Cells(19, 3).Value = "'" & CStr(j) & " - " & CStr(k)
'or
.Cells(19, 3).Value = "W:" & j & " L:" & k

End With

End Sub

Bam_Mab91
09-09-2019, 03:47 PM
This solution did work.

I was analyzing and trying to see where I went wrong to keep getting 0-0. Is it because I didn't assume Activesheet or because of the formatting of my If statements? I just want to improve and be able to look for my own mistakes and not just copy paste solutions but to learn.
I had also tried updating my string outside of my For Loop so I don't believe that was the issue.

Either way, thank you for your assistance and the comments you made they were helpful.