0

I have been working on getting a timesheet macro that will take a data dumps and make do a few things. Ultimately I am not familiar with the syntax of VBA and have got close however am needing help with getting this finished. Below will be my code and comments where I am working on code as well as a screenshot for reference of the spreadsheet.

1 My question is how do I properly write the syntax using vars? For instance in this line of code: If IsNumeric(Cells("Fr").Value) Then I am geting errors and am unsure how I would enter the r value from the loop. This applies to a few of the other lines I was getting errors for but didn't know how to use r to identify a row.

Sub sum()
    Dim r As Integer, c As Integer, s As Double, t As Integer, g As Integer
    r = 2 'looping var
    c = 3 'looping var
    s = 0 'var for sum
    g = 0
    t = ActiveSheet.UsedRange.Rows.Count                    'var for total rows
    
    
    Do Until r = t
        If Not IsEmpty(Range("Ar").Value) = True Then       'check if user name is present then
                                                            'Detect the next cell that contains data in the user name column 
                                                            'Use that number between the two as a var (g) that will be used to run the embedded looping
                                                            'essentially redefining the other loop each time to account for the different number of clock ins per user
        

            Do Until c = g                                  'Loop for until the next name was detected via var (g)
                If IsNumeric(Cells("Fr").Value) Then        'check if Billable has a number then
                s = s + Range("r, F").Value                 'adds cell value (numbers only) to sum
                c = c + 1                                   'add 1 to the value of c
            Loop                                            'closes embedded loop once values have been added up  
        Range("Fr") = s                                     'Replace Cell (Fr) with the sum value
        s = 0                                               'reset the value of the sum
                                                        
        r = r + 1
    Loop
End Sub
Drew MGE
  • 1
  • 2
  • I am aware that some of this syntax is incorrect in my current code, however it has been difficult to find resources on an older programming language such as .net – Drew MGE Jan 08 '21 at 18:07
  • ^^^^ Fixed the tags for you. It's not `.net`, and the [docs](https://learn.microsoft.com/en-us/office/vba/api/overview/excel) are quite accessible. – BigBen Jan 08 '21 at 18:07
  • Please [edit] the question to include an actual question. It is not apparent what you need. Remember that we are here to help overcome A(singular) Problem and not rewrite your code to fix a myriad of issues. You have not explained what it is your are trying to do and how this code fails to do it. – Scott Craner Jan 08 '21 at 18:11
  • I have a plethora of issues as I've never used this language before so I'm fine making 20 posts if that is what is needed. I am attempting to get some guidance or code/links to refer to. I don't want anyone to finish my code for me. I'm hoping to learn as this will be beneficial for future formatting of data. – Drew MGE Jan 08 '21 at 18:19
  • 1
    For your current question if I'm understanding correctly: https://stackoverflow.com/questions/41004389/using-a-variable-for-row-in-row-column-for-a-range-in-vba – Warcupine Jan 08 '21 at 18:21
  • 1
    Your `Range` and `Cells` syntax is off and also variables should not be inside quotes: `Range("A" & r`) and `Range("F" & r)`. Or `Cells(r, "A")` and `Cells(r, "F")`. – BigBen Jan 08 '21 at 18:21
  • @BigBen thank you for clarifying that was one of the tricky things I couldn't find. Also appreciate you editing post to help out the newbie :) Is it better if I post more short to the point questions rather than like the OP? – Drew MGE Jan 08 '21 at 18:29
  • 1
    Please look at [mcve] for guidance on what we expect. – Scott Craner Jan 08 '21 at 18:30
  • @ScottCraner thanks for the link will be sure to follow proper format with future posts. Just to make sure am I on the right track here? Like my thinking of how this code would look/function in VBA? – Drew MGE Jan 08 '21 at 18:37
  • Use meaningful names for your variables then you will be able to delete the majority of your comments such as the utterly meaningless 'c = c + 1 'add 1 to the value of c'. You should also be aware that Long is the default type for integer values so declaring variable as integer does not gain anything. – freeflow Jan 08 '21 at 18:40
  • coding is some times as personal as one's fingerprints. I personally do not like Do/While loops, but like to find the end and do a For loop instead. The best thing is to get the code working by coming here for specific help overcoming specific issues, then when it is working take the whole code to code review with a full explanation of what you have and what you are trying to accomplish and they can help make it better. – Scott Craner Jan 08 '21 at 18:43

0 Answers0