Help with a loop?

[Ah5522]

Hi all,

I'm working on creating a reporting dashboard where userforms are used to enter data into a large set of sheets with multiple tables. The intent is to dump the userform data across multiple tables to allow a dashboard to function properly. Reporting is done monthly.

I've written code that accomplishes the job but I am wondering if there is a way to simplify it as I am using a series of If statements to designate where data goes in the table based on the month the user selects. Because we are reporting for a year period that is 12 different if statements and in some instances up to 10 data points per statement, which is making my code very long and prone to errors when editing. I've included a sample below.

Is there a way I could write this as a loop? I'm thinking something along the line declare 1 = "April", 2="May", 3="June" and using i 1-12 when i = 2 offset by 1 column, i = 3 offset by 2 and so on? I tried to write a loop for it and failed miserably.

Code:

Dim rng As Range
'For PPR Outputs Chart designates table to enter data and specifies what column and row to put data based on month
Set rng = ThisWorkbook.Worksheets("PPR").Range("TablePPRImpact")
With rng
If cboMonth.Value = "" Then
MsgBox "Please select the month"
Exit Sub
End If
If Me.cboMonth.Value = "April" Then
rng.Cells(1, 3).Value = Me.txtUses.Value
rng.Cells(2, 3).Value = Me.txtUsesNotes
End If
If Me.cboMonth.Value = "May" Then
rng.Cells(1, 4).Value = Me.txtUses.Value
rng.Cells(2, 4).Value = Me.txtUsesNotes
End If
If Me.cboMonth.Value = "June" Then
rng.Cells(1, 5).Value = Me.txtUses.Value
rng.Cells(2, 5).Value = Me.txtUsesNotes

The table is set up as follows

Month~March18~April18~May18~June18...
#Uses~(Data)~(Data)~(Data)~(Data)
Notes~(Data)~(Data)~(Data)~(Data)

 

[wideboydixon]

How are you populating the combo box? Does March belong on row 14? If the months are populated in order, you could use cboMonth.ListIndex like this:

rng.Cells(1, cboMonth.ListIndex + 3).Value = Me.txtUses.Value
rng.Cells(2, cboMonth.ListIndex + 3).Value = Me.txtUsesNotes

If not, you could resort to a crude method like this:

Dim rng As Range
Dim monthRow As Long

'For PPR Outputs Chart designates table to enter data and specifies what column and row to put data based on month
Set rng = ThisWorkbook.Worksheets("PPR").Range("TablePPRImpact")

If cboMonth.Value = "" Then
    MsgBox "Please select the month"
    Exit Sub
End If

monthRow = (Month(CDate("1-" & cboMonth.Value & "-2018")) + 8) Mod 12 + 3
rng.Cells(1, monthRow).Value = Me.txtUses.Value
rng.Cells(2, monthRow).Value = Me.txtUsesNotes

WBD

 

[Ah5522]

Hey! Thanks for the quick reply.

The combobox for month is populated from a list (in the same order as they appear on the table)

Private Sub UserForm_Initialize()
'Populate Month combo box.
Dim rngMonth As Range
Dim ws As Worksheet
Set ws = Worksheets("MacroData")
For Each rngMonth In ws.Range("C3:C14")
Me.cboMonth.AddItem rngMonth.Value
Next rngMonth
End Sub

So I think your first suggestion would work. and be a hell of a lot more elegant that the massive block of ifs. I'm just getting into VBA and just finished taking an intro course that went into simple userforms. Learning more every day! Will give it a whirl and let you know how it goes