Solved Trying to "clean up" my code but code isn't working

zjamespryor · Oct 8th 2019

Hello,

I've got a pretty complex user form that is sending information to different rows on different sheets. I'm trying to "clean" my code by making it easier to look at it, but what I'm doing is making the select code not work at all. I'm a novice with VBA and I'm trying to improve my skills! I appreciate any help!

This works:

Code

[VBA]       Dim DateRow As Integer
            DateRow = WorksheetFunction.Match(Range("ArrivalDate"), Sheets("Dates").Range("Dyn_SelectDate"), 0) - 1 'selects the the same date in the calendar
        Dim MemberRowAdd As Integer
            MemberRowAdd = Range("Dyn_TeamMembers")


        Dim i As Integer
            For i = 0 To Range("DateDifference")


                Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value + 1 'loops through the rows to add 1 more value to that date range for the team column
                Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value + MemberRowAdd 'does the same for the team member column but adds team members
            Next i[/VBA]

Display More

This does not:

Code

[VBA]        Dim DateRow As Integer
            DateRow = WorksheetFunction.Match(Range("ArrivalDate"), Sheets("Dates").Range("Dyn_SelectDate"), 0) - 1 'selects the the same date in the calendar
        Dim MemberRowAdd As Integer
            MemberRowAdd = Range("Dyn_TeamMembers")


        Dim i As Integer
            For i = 0 To Range("DateDifference")


        Dim RowFinder As Integer
            RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value
                RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value + 1 'loops through the rows to add 1 more value to that date range for the team column
                RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value + MemberRowAdd 'does the same for the team member column but adds team members
            Next i[/VBA]

Display More

Everything is the same between the two except for the last section of code.

I tried [VBA]Dim RowFinder as String[/VBA] but that didn't work either.

Am I setting my variables improperly or is what I'm trying to do impossible?

Thanks!

**royUK** · Oct 8th 2019

Which userform are you cleaning?

Why are you not using the ControlTipText? It would save a lot of coding.

Code

Me.Txt_TeamCode.ControlTipText = "Insert team code here"

zjamespryor · Oct 8th 2019

Quote from royUK
Which userform are you cleaning?

Why are you not using the ControlTipText? It would save a lot of coding.
Code
Me.Txt_TeamCode.ControlTipText = "Insert team code here"

I'm trying to cleanup the AddNew_UF userform. Where would I put that code and how is it used?

**royUK** · Oct 8th 2019

That code adds the ControlTipText and could be used to replace your code that populates the info label

zjamespryor · Oct 8th 2019

Where would I put it as code in the user form? Under UserForm_Initialize and just list out each object?

**royUK** · Oct 8th 2019

You should group your variables at the start of each procedure.

I cannot find RowFinder in the AddNew_UF userform

I've made some changes, if you use the ControlTipText than you don't need to use all your Hover Popup code

zjamespryor · Oct 8th 2019

Yeah, this is significantly better. Thank you!

The RowFinder was code I had deleted because it wasn't working, but I realized (probably when you were helping me) that I had a typo in the original formula for the wrong column number.

**royUK** · Oct 9th 2019

Pleased to help.

Solved Trying to "clean up" my code but code isn't working

Participate now!

Share