Simplify VBA Code (Loop through a column range and assign variables to cell address values)

[kathendy]

Hi,

This is my first post (I hope there isn't anything like this, because I have spent hours searching).

I offered to fix someone's excel spreadsheet and low and behold, half the spreadsheet has formulas and the other half is in VBA and they are intertwined, and both dependant on each other... It's been an absolutely nightmare. I have managed to sort most of it out myself (not having touched VBA in over 15years) using the internet and mostly here, however I am now stuck (or more to the point, brain dead) and need some help.

My problem is this:

The spreadsheet is used to calculate handicaps for a golf club. The Player's Name, Starting Handicap and Grade are already entered. Below is a partial of the spreadsheet (it continues through to December AG:AI).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Player Name[/TD]
[TD]HC[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]HC[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]HC[/TD]
[/TR]
</tbody>[/TABLE]

I have it set up so that the score is manually entered into the spreadsheet for each month and then through the use of a Userform, the month is selected (option buttons) which runs the Module procedure UpdateHC to calculate new Handicaps and Grades (if needed).

This all works perfectly. However, the code is extremely bulky and I don't want to copy it for each month and update to suit each column. As the structure of the code works, I've only posted a partial of the code because it is is over 130 lines long. If the rest is needed, more than happy to provide it.

LastRow = Sheets("Scores").Range("A" & Rows.Count).End(xlUp).Row


For i = 4 To LastRow


If Sheets("Scores").Cells(i, "C").Value = GrA Then 'A Grade
    Select Case Sheets("Scores").Cells(i, "D").Value >= 1
        Case Sheets("Scores").Cells(i, "D").Value < 36
            If Sheets("Scores").Cells(i, "D").Value < 36 And Sheets("Scores").Cells(i, "D").Interior.Color = vbRed And Sheets("Scores").Cells(i, "B").Value = 1 Then
                Sheets("Scores").Cells(i, "E").Value = Sheets("Scores").Cells(i, "B").Value

Problem 1 - The code is too bulky. What I am struggling to achieve is to simplify it to something like this:

OrgHC=Sheets("Scores").Cells(i, "B").Value
Gr=Sheets("Scores").Cells(i, "C").Value
Scr=Sheets("Scores").Cells(i, "D").Value
HC=Sheets("Scores").Cells(i, "E").Value
ScrCl=Sheets("Scores").Cells(i, "D").Interior.Color

If Gr=GrA Then
   Select Case Scr >= 1
      Case Scr<36
         If Scr<36 And ScrCl=vbRed And OrgHC=1
            HC=OrgHC

Or something along the lines of that anyway. I have tried all combinations of variables, cell locations with and without the .Value included, different locations in the code (above the For i statement, below the For i statement, under the first if statement, under the Case statements, and so on....). I'm stuck.

Problem 2 - I don't want to have to copy the whole code and change the columns it references for each month. How can I create a loop or such (similar to what I have set up for the rows) to move to the next month (ie February Score to March Score) when I select the month from my userform. As mentioned above, the columns go through to AG:AI (each month has 3 columns). I need to be able to independently update each month. I am completely at a lost, so I am seeking expert advice.

Any form of advice would be greatly appreciated even if it is just as "too bad, so sad, start copying and changing references", because I will if there is no other way. I hope I have provided enough information.

Thank you in advance

Ps. I am using MS Excel 2007, VBA 6.5 and Windows Vista Business.

 

[Snakehips]

kathendy,

Welcome to MrExcel.

Difficult to offer specific solution but there should be absolutely no need for "too bad,so sad etc"
It looks as if the inputs are Feb to Dec - no Jan ???

Once you have determined the revision 'Month' via the userform you can use that to reference the three columns you need.

Say Mnth = month number Feb = 2 march 3 etc.
GradeCol = 3*(Mnth-1) ScrCol = 4*(Mnth-1) HcpCol = 5*(Mnth-1)

So then you could use cell references e.g.

 Cells(i, GradeCol)   Cells(i, ScrCol)   Cells(i, HcpCol)

Alternatively you could use the month to give you the offset from the Feb columns, which for Feb would be 0 and for March would be 1 etc.

Dim ws As Worksheet
Mnth = 3  '??? capture from userform so March = 3
'assuming months run Feb to Dec ???
'then use column offset co from the three Feb columns
co = Mnth - 2  'eg 0 for Feb  1 For march
'set worksheet variable to help de-bulk
Set ws = Sheets("Scores")
'set values (or ranges if it suits)
OrgHC = ws.Cells(i, "B").Value
Gr = ws.Cells(i, "C").Offset(0, co).Value
Scr = ws.Cells(i, "D").Offset(0, co).Value
HC = ws.Cells(i, "E").Offset(0, co).Value
ScrCl = ws.Cells(i, "D").Offset(0, co).Interior.Color


'If you need to refer to the previous months values for calc purposes then
'the previous values are eg......
PrevGr = ws.Cells(i, "C").Offset(0, co - 1).Value  'Excluding Feb which has no previous values!

Hope that helps.

 

[kathendy]

Hi

It took a bit of tweaking but I managed to get it some what simplified using a combination of both your suggestions. It highlighted a few areas in my code that weren't quite right, but are now corrected.

Thank you very much!!

PROBLEM SOLVED