Input Data From UserForm In Correct Format in Excel in VBA

ABlohm

New Member
Joined
Mar 16, 2017
Messages
5
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?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

ABlohm

New Member
Joined
Mar 16, 2017
Messages
5
The format did not come through on the above post.

Current outcome
Monday A C
Tuesday B

Desired outcome
Monday A C
Tuesday B
 

ABlohm

New Member
Joined
Mar 16, 2017
Messages
5
I wasn't able to format it again, but I want the each row to begin with the column previously, right now it skips a column. For example if I put in Monday it will input in Column B. Next I input Tuesday and instead of going to Column B it will got to Column C. The third input for the example would be in Monday and should be placed in Column C but instead goes into Column D.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,662
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top