I have a UserForm that asks the user to select a day (Monday, Tuesday, Wednesday...) and input a position. I want to direct this information to the worksheet called Data. I want to the position to be inputed in the row that is associated with the day the user selected for it.
The current code I have is:
Dim ColumnCountM As Long
Dim ColumnCountT As Long
Dim ColumnCountW As Long
Dim ColumnCountTH As Long
Dim ColumnCountF As Long
Dim ColumnCountS As Long
Dim ColumnCountSU As Long
ColumnCountM = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountT = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountW = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountTH = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountF = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountS = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountSU = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
With Worksheets("Data").Range("A1")
If Me.ComboBoxDay.Value = "Monday" Then
.Offset(0, ColumnCountM).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Tuesday" Then
.Offset(1, ColumnCountT).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Wednesday" Then
.Offset(2, ColumnCountW).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Thursday" Then
.Offset(3, ColumnCountTH).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Friday" Then
.Offset(4, ColumnCountF).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Saturday" Then
.Offset(5, ColumnCountS).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Sunday" Then
.Offset(6, ColumnCountSU).Value = Me.TextPosition.Value
End If
End With
An example of the outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday
And the desired outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday
I note that "currentregion.columns.count" is the same for the column counts for all days. I believe this is the main error. How can I fix this to get my desired outcome?
The current code I have is:
Dim ColumnCountM As Long
Dim ColumnCountT As Long
Dim ColumnCountW As Long
Dim ColumnCountTH As Long
Dim ColumnCountF As Long
Dim ColumnCountS As Long
Dim ColumnCountSU As Long
ColumnCountM = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountT = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountW = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountTH = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountF = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountS = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountSU = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
With Worksheets("Data").Range("A1")
If Me.ComboBoxDay.Value = "Monday" Then
.Offset(0, ColumnCountM).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Tuesday" Then
.Offset(1, ColumnCountT).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Wednesday" Then
.Offset(2, ColumnCountW).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Thursday" Then
.Offset(3, ColumnCountTH).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Friday" Then
.Offset(4, ColumnCountF).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Saturday" Then
.Offset(5, ColumnCountS).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Sunday" Then
.Offset(6, ColumnCountSU).Value = Me.TextPosition.Value
End If
End With
An example of the outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday
And the desired outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday
I note that "currentregion.columns.count" is the same for the column counts for all days. I believe this is the main error. How can I fix this to get my desired outcome?