Using Autofill to fill a Blank space with Formula (Help Please)

AutoMation42

New Member
Joined
Oct 14, 2021
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is now my second post to this forum exciting stuff. I am still very new to coding so please excuse any foolish code writing. My problem here is that I wrote a piece of code that is supposed to autofill some formulas but the number of rows is constantly changing as data is added so it has to be dynamic. Basically, I have a bunch of code already written that prompts a bunch of InputBox windows to pop up so that anyone can fill out the necessary information and then it fills in the according information in the row (skipping certain spaces where I just want a formula to be dragged down. Everything works except for this tiny little piece of code that I can't seem to make work no matter what I do with it. If any more information is required I would be more than happy to provide examples. Any help would be greatly appreciated!

The code that is not working appears like this:

Set fillRange = Worksheets("Sheet1").Range("L3:L").End(xlDown)
Range("L3:L4").AutoFill Destination:=fillRange
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I am assuming this is all happening in 'Sheet1'
I am also unsure just how you want to determine the bottom of the fill range. This would use the last row in column A with data to determine the last row of the fill range.
If that is not it, then please explain in more detail how the bottom of the range is to be determined and/or give us a small dummy sample with XL2BB

VBA Code:
With Worksheets("Sheet1")
  Set fillRange = .Range("L3:L" & .Range("A" & Rows.Count).End(xlUp).Row)
  .Range("L3:L4").AutoFill Destination:=fillRange
End With
 
Upvote 0
I am assuming this is all happening in 'Sheet1'
I am also unsure just how you want to determine the bottom of the fill range. This would use the last row in column A with data to determine the last row of the fill range.
If that is not it, then please explain in more detail how the bottom of the range is to be determined and/or give us a small dummy sample with XL2BB

VBA Code:
With Worksheets("Sheet1")
  Set fillRange = .Range("L3:L" & .Range("A" & Rows.Count).End(xlUp).Row)
  .Range("L3:L4").AutoFill Destination:=fillRange
End With
VBA Code:
Sub Please_Help_Me_Make_This_More_Efficient()

Dim Variable1 As Single 'This one is only numbers up to 3 digits (ie 123)
Dim Variable2 As Single 'This one is only numbers up to 6 digits (ie 123456)
Dim Variable3 As String 'This one is a mix of letters and special characters up to 3 characters
Dim Variable4 As String 'This one is a string words, numbers, and special characters varying in size (ie Here's / an example 123)
Dim Variable5 As String 'This one is a string words, numbers, and special characters varying in size (ie Here's / an example 123)
Dim Variable6 As String 'This one is only letters
Dim Variable7 As Single 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable8 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable9 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable10 As Single 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable11 As Single 'This one is numbers only up to 3 digits
Dim Variable12 As Long 'This one is a formula
Dim Variable13 As Long 'This one is numbers only up to 8 digits
Dim Variable14 As Long 'This one is numbers only up to 8 digits
Dim Variable15 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable16 As Long 'THIS ONE IS ODD AND EXPLAINED IN THE HELP REQUEST
Dim Variable17 As Long 'This one is a formula
Dim Variable18 As Long 'This one is numbers only up to 8 digits
Dim Variable19 As Long 'This one is a formula
Dim Variable20 As Long 'This one is a formula
Dim Variable21 As String 'This one is a mix of letter, numbers, and special characters with a drastically varying number of digits/characters used up to a maximum of 50 and usually with commas separating and spaces separating them
Dim Variable22 As Long 'This one is numbers only up to 8 digits
Dim Variable23 As Long 'This one is a formula
Dim Variable24 As Long 'This one is numbers only up to 8 digits
Dim Variable25 As Single 'This one is numbers only up to 8 digits
Dim Variable26 As Long 'This one is a formula
Dim Variable27 As Long 'This one is a formula
Dim Variable28 As Date 'This one is a Date

Sheets("Active Stuff").Activate 'This line is included so that I may use the button on another sheet to call this sub

'The below code is calling all of the various input boxes to put information in
    Variable1 = InputBox("Enter a value for Variable1", "Variable1") 'Column A entry
    Variable2 = InputBox("Enter a value Variable2", "Variable2") 'Column B Entry
    Variable3 = InputBox("Enter the value for Variable3", "Variable 3") 'Column C Entry
    Variable4 = InputBox("Enter the value for Variable4", "Variable4") 'Column D Entry
    Variable5 = InputBox("Enter the value for Variable5", "Variable5") 'Column E Entry
    Variable6 = InputBox("Enter the value for Variable6", "Variable6") 'Column F Entry
    Variable7 = InputBox("Enter the value for Variable7", "Variable7") 'Column G Entry
    Variable8 = InputBox("Enter the value for Variable8", "Variable8") 'Column H Entry
    Variable9 = InputBox("Enter value for Variable9", "Variable9") 'Column I Entry
    Variable10 = InputBox("Enter the value for Variable10", "Variable10") 'Column J Entry
    Variable11 = InputBox("Enter the value for Variable11", "Variable11") 'Column K Entry
    Variable13 = InputBox("Enter the value for Variable13", "Variable13") 'Column M Entry
    Varaible14 = InputBox("Enter the value for Variable14", "Variable14") 'Column N Entry
    Variable15 = InputBox("Enter the value for Variable15", "Variable15") 'Column O Entry
    Variable16 = InputBox("Enter the value for Variable16", "Variable16") 'May be able to automate this value with a simple function formula Column P Entry
    Variable18 = InputBox("Enter the value for Variable18", "Variable18") 'Column R Entry
    Variable21 = InputBox("Enter the value for Variable21", "Variable21") 'Column U Entry
    Variable22 = InputBox("Enter the value for Variable22", "Variable22") 'Column V Entry
    Variable24 = InputBox("Enter the value for Variable24", "Variable24") 'Column X Entry
    Variable25 = InputBox("Enter the value for Variable25", "Variable25") 'Column Y Entry
    Variable28 = InputBox("Enter the value for Variable28", "Variable28") 'Column AB Entry

  
'The below code is setting cells equal to the inputbox values
        lastrow0 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
            ActiveCell = Variable1
        lastrow1 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1).Select
            ActiveCell = Variable2
        lastrow2 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 2).Select
            ActiveCell = Variable3
        lastrow3 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 3).Select
            ActiveCell = Variable4
        lastrow4 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 4).Select
            ActiveCell = Variable5
        lastrow5 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 5).Select
            ActiveCell = Variable6
        lastrow6 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 6).Select
            ActiveCell = Variable7
        lastrow7 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 7).Select
            ActiveCell = Variable8
        lastrow8 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 8).Select
            ActiveCell = Variable9
        lastrow9 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 9).Select
            ActiveCell = Variable10
        lastrow10 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 10).Select
            ActiveCell = Variable11
        lastrow12 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 12).Select
            ActiveCell = Variable13
        lastrow13 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 13).Select
            ActiveCell = Variable14
        lastrow14 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 14).Select
            ActiveCell = Variable15
        lastrow15 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 15).Select
            ActiveCell = Variable16
        lastrow17 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 17).Select
            ActiveCell = Variable18
        lastrow20 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 20).Select
            ActiveCell = Variable21
        lastrow21 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 21).Select
            ActiveCell = Variable22
        lastrow23 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 23).Select
            ActiveCell = Variable24
        lastrow24 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 24).Select
            ActiveCell = Variable25
        lastrow27 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 27).Select
            ActiveCell = Variable28

'The below code is dragging formulas down to the row that information is being added to with the inputbox information
     Variable12 = Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).Row
        Range("L3").Select
        Selection.AutoFill Destination:=Range("L3:L" & Variable12), Type:=xlFillDefault

     Variable17 = Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Row
        Range("Q3").Select
        Selection.AutoFill Destination:=Range("Q3:Q" & Variable17), Type:=xlFillDefault
        
     Variable19 = Cells(Rows.Count, "S").End(xlUp).Offset(1, 0).Row
        Range("S3").Select
        Selection.AutoFill Destination:=Range("S3:S" & Variable19), Type:=xlFillDefault
        
     Variable20 = Cells(Rows.Count, "T").End(xlUp).Offset(1, 0).Row
        Range("T3").Select
        Selection.AutoFill Destination:=Range("T3:T" & Variable20), Type:=xlFillDefault
        
     Variable23 = Cells(Rows.Count, "W").End(xlUp).Offset(1, 0).Row
        Range("W3").Select
        Selection.AutoFill Destination:=Range("W3:W" & Variable23), Type:=xlFillDefault
        
     Variable26 = Cells(Rows.Count, "Z").End(xlUp).Offset(1, 0).Row
        Range("Z3").Select
        Selection.AutoFill Destination:=Range("Z3:Z" & Variable26), Type:=xlFillDefault
        
     Variable27 = Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).Row
        Range("AA3").Select
        Selection.AutoFill Destination:=Range("AA3:AA" & Variable27), Type:=xlFillDefault

'The below code is re-sorting the data including the new piece that has been added by the above code  
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range("A3:A65536"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range("B3:B65536"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Active Stuff").Sort
        .SetRange Range("A2:AB65536")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
                    
End Sub

Hi @Peter_SSs and thank you for the help! I am also going to @Akuini because they have been helping me right along in a different post and despite not knowing them at all have come to view them as a mentor of sorts, and you have done plenty to help me so please feel free to ignore me. I have some code that I managed to piece together with help from various sites including this one. I have been wording my questions a bit poorly so I am going to do my best to explain this part of the code all in one shot and accurately so that it can all be completed all at once. The code I have is actually in Module1 now because I made a dashboard for all the buttons I intend to have and am calling the Subs with a button on the dashboard that is simply "Call Module1.whatever_sub_this_button_is_for". This being said the Dashboard sheet is “Dashboard Control” and the Sheet all this is happening on is “Active Stuff” I have changed many titles, names, and indicators to keep confidential information, confidential so if anything seems silly, that’s because it probably is. I understand this is a large request considering this is a forum but any help that can be provided would be greatly appreciated, and thank you for taking the time to even consider it.

  1. I am going to post the full code because it is lengthy and could probably be condensed into a much simpler form. What I want this code to do is prompt the user with an inputbox that will expressly ask for specific content and fill it in accordingly across the next unused row. These rows of data have headers/titles at the top of the column which change order (I will attach a sample of sheet setup) and although my code works, it is not dynamic at all and adds the data to the same place no matter what. In the code next to the variables I explained what I need to be able to do with each one. So, if the column Variable2 switches places with Variable4 I will now be adding data to the wrong place and also sorting by the wrong order.
  2. After all the data gets added and the input box portion is done, I want the specific listed rows above to simply pull the formula down, and again it works as is but is not dynamic.
  3. Once all the data including formulas is filled in I want the data sorted from smallest to largest from Variable1 first and then from smallest to largest again but using Variable2 (again these rows can change places).
  4. Right now I have Variable16 listed as something to be added as an input box but it is going to be a formula that I need to drag down like Variable12
 

Attachments

  • Format Example.JPG
    Format Example.JPG
    41.6 KB · Views: 7
Upvote 0
VBA Code:
Sub Please_Help_Me_Make_This_More_Efficient()

Dim Variable1 As Single 'This one is only numbers up to 3 digits (ie 123)
Dim Variable2 As Single 'This one is only numbers up to 6 digits (ie 123456)
Dim Variable3 As String 'This one is a mix of letters and special characters up to 3 characters
Dim Variable4 As String 'This one is a string words, numbers, and special characters varying in size (ie Here's / an example 123)
Dim Variable5 As String 'This one is a string words, numbers, and special characters varying in size (ie Here's / an example 123)
Dim Variable6 As String 'This one is only letters
Dim Variable7 As Single 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable8 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable9 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable10 As Single 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable11 As Single 'This one is numbers only up to 3 digits
Dim Variable12 As Long 'This one is a formula
Dim Variable13 As Long 'This one is numbers only up to 8 digits
Dim Variable14 As Long 'This one is numbers only up to 8 digits
Dim Variable15 As Long 'This one is a formula of sorts (=1+2+3+4 and the numbers have to be separate like that to keep track of individual pieces)
Dim Variable16 As Long 'THIS ONE IS ODD AND EXPLAINED IN THE HELP REQUEST
Dim Variable17 As Long 'This one is a formula
Dim Variable18 As Long 'This one is numbers only up to 8 digits
Dim Variable19 As Long 'This one is a formula
Dim Variable20 As Long 'This one is a formula
Dim Variable21 As String 'This one is a mix of letter, numbers, and special characters with a drastically varying number of digits/characters used up to a maximum of 50 and usually with commas separating and spaces separating them
Dim Variable22 As Long 'This one is numbers only up to 8 digits
Dim Variable23 As Long 'This one is a formula
Dim Variable24 As Long 'This one is numbers only up to 8 digits
Dim Variable25 As Single 'This one is numbers only up to 8 digits
Dim Variable26 As Long 'This one is a formula
Dim Variable27 As Long 'This one is a formula
Dim Variable28 As Date 'This one is a Date

Sheets("Active Stuff").Activate 'This line is included so that I may use the button on another sheet to call this sub

'The below code is calling all of the various input boxes to put information in
    Variable1 = InputBox("Enter a value for Variable1", "Variable1") 'Column A entry
    Variable2 = InputBox("Enter a value Variable2", "Variable2") 'Column B Entry
    Variable3 = InputBox("Enter the value for Variable3", "Variable 3") 'Column C Entry
    Variable4 = InputBox("Enter the value for Variable4", "Variable4") 'Column D Entry
    Variable5 = InputBox("Enter the value for Variable5", "Variable5") 'Column E Entry
    Variable6 = InputBox("Enter the value for Variable6", "Variable6") 'Column F Entry
    Variable7 = InputBox("Enter the value for Variable7", "Variable7") 'Column G Entry
    Variable8 = InputBox("Enter the value for Variable8", "Variable8") 'Column H Entry
    Variable9 = InputBox("Enter value for Variable9", "Variable9") 'Column I Entry
    Variable10 = InputBox("Enter the value for Variable10", "Variable10") 'Column J Entry
    Variable11 = InputBox("Enter the value for Variable11", "Variable11") 'Column K Entry
    Variable13 = InputBox("Enter the value for Variable13", "Variable13") 'Column M Entry
    Varaible14 = InputBox("Enter the value for Variable14", "Variable14") 'Column N Entry
    Variable15 = InputBox("Enter the value for Variable15", "Variable15") 'Column O Entry
    Variable16 = InputBox("Enter the value for Variable16", "Variable16") 'May be able to automate this value with a simple function formula Column P Entry
    Variable18 = InputBox("Enter the value for Variable18", "Variable18") 'Column R Entry
    Variable21 = InputBox("Enter the value for Variable21", "Variable21") 'Column U Entry
    Variable22 = InputBox("Enter the value for Variable22", "Variable22") 'Column V Entry
    Variable24 = InputBox("Enter the value for Variable24", "Variable24") 'Column X Entry
    Variable25 = InputBox("Enter the value for Variable25", "Variable25") 'Column Y Entry
    Variable28 = InputBox("Enter the value for Variable28", "Variable28") 'Column AB Entry

 
'The below code is setting cells equal to the inputbox values
        lastrow0 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
            ActiveCell = Variable1
        lastrow1 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1).Select
            ActiveCell = Variable2
        lastrow2 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 2).Select
            ActiveCell = Variable3
        lastrow3 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 3).Select
            ActiveCell = Variable4
        lastrow4 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 4).Select
            ActiveCell = Variable5
        lastrow5 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 5).Select
            ActiveCell = Variable6
        lastrow6 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 6).Select
            ActiveCell = Variable7
        lastrow7 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 7).Select
            ActiveCell = Variable8
        lastrow8 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 8).Select
            ActiveCell = Variable9
        lastrow9 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 9).Select
            ActiveCell = Variable10
        lastrow10 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 10).Select
            ActiveCell = Variable11
        lastrow12 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 12).Select
            ActiveCell = Variable13
        lastrow13 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 13).Select
            ActiveCell = Variable14
        lastrow14 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 14).Select
            ActiveCell = Variable15
        lastrow15 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 15).Select
            ActiveCell = Variable16
        lastrow17 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 17).Select
            ActiveCell = Variable18
        lastrow20 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 20).Select
            ActiveCell = Variable21
        lastrow21 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 21).Select
            ActiveCell = Variable22
        lastrow23 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 23).Select
            ActiveCell = Variable24
        lastrow24 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 24).Select
            ActiveCell = Variable25
        lastrow27 = ThisWorkbook.Worksheets("Active Stuff ").Cells(Rows.Count, "A").End(xlUp).Offset(0, 27).Select
            ActiveCell = Variable28

'The below code is dragging formulas down to the row that information is being added to with the inputbox information
     Variable12 = Cells(Rows.Count, "L").End(xlUp).Offset(1, 0).Row
        Range("L3").Select
        Selection.AutoFill Destination:=Range("L3:L" & Variable12), Type:=xlFillDefault

     Variable17 = Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Row
        Range("Q3").Select
        Selection.AutoFill Destination:=Range("Q3:Q" & Variable17), Type:=xlFillDefault
       
     Variable19 = Cells(Rows.Count, "S").End(xlUp).Offset(1, 0).Row
        Range("S3").Select
        Selection.AutoFill Destination:=Range("S3:S" & Variable19), Type:=xlFillDefault
       
     Variable20 = Cells(Rows.Count, "T").End(xlUp).Offset(1, 0).Row
        Range("T3").Select
        Selection.AutoFill Destination:=Range("T3:T" & Variable20), Type:=xlFillDefault
       
     Variable23 = Cells(Rows.Count, "W").End(xlUp).Offset(1, 0).Row
        Range("W3").Select
        Selection.AutoFill Destination:=Range("W3:W" & Variable23), Type:=xlFillDefault
       
     Variable26 = Cells(Rows.Count, "Z").End(xlUp).Offset(1, 0).Row
        Range("Z3").Select
        Selection.AutoFill Destination:=Range("Z3:Z" & Variable26), Type:=xlFillDefault
       
     Variable27 = Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0).Row
        Range("AA3").Select
        Selection.AutoFill Destination:=Range("AA3:AA" & Variable27), Type:=xlFillDefault

'The below code is re-sorting the data including the new piece that has been added by the above code 
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range("A3:A65536"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range("B3:B65536"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Active Stuff").Sort
        .SetRange Range("A2:AB65536")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
                   
End Sub

Hi @Peter_SSs and thank you for the help! I am also going to @Akuini because they have been helping me right along in a different post and despite not knowing them at all have come to view them as a mentor of sorts, and you have done plenty to help me so please feel free to ignore me. I have some code that I managed to piece together with help from various sites including this one. I have been wording my questions a bit poorly so I am going to do my best to explain this part of the code all in one shot and accurately so that it can all be completed all at once. The code I have is actually in Module1 now because I made a dashboard for all the buttons I intend to have and am calling the Subs with a button on the dashboard that is simply "Call Module1.whatever_sub_this_button_is_for". This being said the Dashboard sheet is “Dashboard Control” and the Sheet all this is happening on is “Active Stuff” I have changed many titles, names, and indicators to keep confidential information, confidential so if anything seems silly, that’s because it probably is. I understand this is a large request considering this is a forum but any help that can be provided would be greatly appreciated, and thank you for taking the time to even consider it.

  1. I am going to post the full code because it is lengthy and could probably be condensed into a much simpler form. What I want this code to do is prompt the user with an inputbox that will expressly ask for specific content and fill it in accordingly across the next unused row. These rows of data have headers/titles at the top of the column which change order (I will attach a sample of sheet setup) and although my code works, it is not dynamic at all and adds the data to the same place no matter what. In the code next to the variables I explained what I need to be able to do with each one. So, if the column Variable2 switches places with Variable4 I will now be adding data to the wrong place and also sorting by the wrong order.
  2. After all the data gets added and the input box portion is done, I want the specific listed rows above to simply pull the formula down, and again it works as is but is not dynamic.
  3. Once all the data including formulas is filled in I want the data sorted from smallest to largest from Variable1 first and then from smallest to largest again but using Variable2 (again these rows can change places).
  4. Right now I have Variable16 listed as something to be added as an input box but it is going to be a formula that I need to drag down like Variable12
VBA Code:
Private Sub TestButtonDynamics_Click()

Dim V1 As Single
Dim V12 As Long
Dim lio, lih
    V1 = InputBox("Please Enter Variable 1 Number Here", "Variable 1")
    lio = Application.Match("Variable 1", Rows(2), 0)

        lastrow0 = Worksheets("Active Stuff").Cells(2, lio).End(xlDown).Offset(1, 0).Select
            ActiveCell = V1

     lih = Application.Match("Variable 12", Rows(2), 0)
     Net = Cells(2, lih).End(xlDown).Offset(1, 0).Row
        Range("L3").AutoFill Destination:=Range("L3:L" & Net), Type:=xlFillDefault

End Sub

I have made some progress in making the above code dynamic and this is just a small excerpt because if this is made dynamic then I can apply it to the rest of the code myself. The code posted with this comment is only partially dynamic. The top part involving Variable 1 and V1 is dynamic and works as I wished for it to and has been tested and confirmed so that part is ok. The second section as you can see is referring specifically to column L and I haven't been able to get any variations to work with autofill. I attempted to use column header names by naming cells to refer to instead of the column letter but it gave me "Method range of object failed errors" on the autofill line. I also attempted to use offset but I got the same error in the same spot and now I am stuck. Any help would be massively appreciated! Thank you for the consideration.
 
Upvote 0
VBA Code:
Private Sub TestButtonDynamics_Click()

Dim V1 As Single
Dim V12 As Long
Dim lio, lih
    V1 = InputBox("Please Enter Variable 1 Number Here", "Variable 1")
    lio = Application.Match("Variable 1", Rows(2), 0)

        lastrow0 = Worksheets("Active Stuff").Cells(2, lio).End(xlDown).Offset(1, 0).Select
            ActiveCell = V1

     lih = Application.Match("Variable 12", Rows(2), 0)
     Net = Cells(2, lih).End(xlDown).Offset(1, 0).Row
        Range("L3").AutoFill Destination:=Range("L3:L" & Net), Type:=xlFillDefault

End Sub

I have made some progress in making the above code dynamic and this is just a small excerpt because if this is made dynamic then I can apply it to the rest of the code myself. The code posted with this comment is only partially dynamic. The top part involving Variable 1 and V1 is dynamic and works as I wished for it to and has been tested and confirmed so that part is ok. The second section as you can see is referring specifically to column L and I haven't been able to get any variations to work with autofill. I attempted to use column header names by naming cells to refer to instead of the column letter but it gave me "Method range of object failed errors" on the autofill line. I also attempted to use offset but I got the same error in the same spot and now I am stuck. Any help would be massively appreciated! Thank you for the consideration.
VBA Code:
Sub Please_Help_Me_Make_This_More_Efficient()

'Dynamic (only works if there is at least one conversion already on the list otherwise it is a type mismatch)

Sheets("Active Stuff").Activate

Range("A1").End(xlDown).Offset(1, 0).EntireRow.Insert

Dim Variable1 As Single
Dim Variable2 As Single
Dim Variable 3 As String
Dim Variable4 As String
Dim Variable5 As String
Dim Variable6 As String
Dim Variable7 As Single
Dim Variable8 As Long
Dim Variable9 As Long
Dim Variable10 As Single
Dim Variable11 As Single
Dim Variable13 As Long
Dim Variable14 As Long
Dim Variable15 As Long
Dim Variable16 As Long
Dim Variable18 As Long
Dim Variable21 As String
Dim Variable22 As Long
Dim Variable24 As Long
Dim Variable25 As Single
Dim Variable28 As Date
Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, v, u


    Variable1 = InputBox("Enter Variable1", "Variable1") 'Column A entry
        a = Application.Match("Variable1", Rows(2), 0)
    lastrow0 = Worksheets("Active Stuff").Cells(2, a).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable1
            
    Variable2 = InputBox("Enter Variable2", "Variable2") 'Column B Entry
        b = Application.Match("Variable2", Rows(2), 0)
    lastrow1 = Worksheets("Active Stuff").Cells(2, b).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable2
            
    Variable3 = InputBox("Enter Variable3", "Variable3") 'Column C Entry
        c = Application.Match("Variable3", Rows(2), 0)
    lastrow2 = Worksheets("Active Stuff").Cells(2, c).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable3
            
    Variable4 = InputBox("Enter Variable4", "Variable4") 'Column D Entry
        d = Application.Match("Variable4", Rows(2), 0)
    lastrow3 = Worksheets("Active Stuff").Cells(2, d).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable4
            
    Variable5 = InputBox("Enter Variable5", "Variable5") 'Column E Entry
        e = Application.Match("Variable5", Rows(2), 0)
    lastrow4 = Worksheets("Active Stuff").Cells(2, e).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable5
            
    Variable6 = InputBox("Enter Variable6", "Variable6") 'Column F Entry
        f = Application.Match("Variable6", Rows(2), 0)
    lastrow5 = Worksheets("Active Stuff").Cells(2, f).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable6
            
    Variable7 = InputBox("Enter Variable7", "Variable7") 'Column G Entry
        g = Application.Match("Variable7", Rows(2), 0)
    lastrow6 = Worksheets("Active Stuff").Cells(2, g).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable7
            
    Variable8 = InputBox("Enter Variable8", "Variable8") 'Column H Entry
        h = Application.Match("Variable8", Rows(2), 0)
    lastrow7 = Worksheets("Active Stuff").Cells(2, h).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable8
            
    Variable9 = InputBox("Enter Variable9", "Variable9") 'Column I Entry
        i = Application.Match("Variable9", Rows(2), 0)
    lastrow8 = Worksheets("Active Stuff").Cells(2, i).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable9
            
    Variable10 = InputBox("Enter Variable10", "Variable10") 'Column J Entry
        j = Application.Match("Variable10", Rows(2), 0)
    lastrow9 = Worksheets("Active Stuff").Cells(2, j).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable10
            
    Variable11 = InputBox("Enter Variable11", "Variable11") 'Column K Entry
        k = Application.Match("Variable11", Rows(2), 0)
    lastrow10 = Worksheets("Active Stuff").Cells(2, k).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable11
            
    Variable13 = InputBox("Enter Variable13", "Variable13") 'Column M Entry
        l = Application.Match("Variable13", Rows(2), 0)
    lastrow12 = Worksheets("Active Stuff").Cells(2, l).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable13
            
    Variable14 = InputBox("Enter Variable14", "Variable14") 'Column N Entry
        m = Application.Match("Variable14", Rows(2), 0)
    lastrow13 = Worksheets("Active Stuff").Cells(2, m).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable14
            
    Variable15 = InputBox("Enter Variable15", "Variable15") 'Column O Entry
        n = Application.Match("Variable15", Rows(2), 0)
    lastrow14 = Worksheets("Active Stuff").Cells(2, n).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable15
            
    Variable16 = InputBox("Enter Variable16", "Variable16") 'May be able to automate this value with a simple function formula Column P Entry
        o = Application.Match("Variable16", Rows(2), 0)
    lastrow15 = Worksheets("Active Stuff").Cells(2, o).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable16
            
    Variable18 = InputBox("Enter Variable18", "Variable18") 'Column R Entry
        p = Application.Match("Variable18", Rows(2), 0)
    lastrow17 = Worksheets("Active Stuff").Cells(2, p).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable18
            
    Variable21 = InputBox("Enter Variable21", "Variable21") 'Column U Entry
        q = Application.Match("Variable21", Rows(2), 0)
    lastrow20 = Worksheets("Active Stuff").Cells(2, q).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable21
            
    Variable22 = InputBox("Enter Variable22", "Variable22") 'Column V Entry
        r = Application.Match("Variable22", Rows(2), 0)
    lastrow21 = Worksheets("Active Stuff").Cells(2, r).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable22
            
    Variable24 = InputBox("Enter Variable24", "Variable24") 'Column X Entry
        s = Application.Match("Variable24", Rows(2), 0)
    lastrow23 = Worksheets("Active Stuff").Cells(2, s).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable24
            
    Variable25 = InputBox("Enter Variable25", "Variable25") 'Column Y Entry
        v = Application.Match("Variable25", Rows(2), 0)
    lastrow24 = Worksheets("Active Stuff").Cells(2, v).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable25
            
    Variable28 = InputBox("Enter Variable28", "Variable28") 'Column AB Entry
        u = Application.Match("Variable28", Rows(2), 0)
    lastrow27 = Worksheets("Active Stuff").Cells(2, u).End(xlDown).Offset(1, 0).Select
            ActiveCell = Variable28


    Dim rngVariable12 As Range
        Set rngVariable12= Range("A2:AZ2").Find("Variable12", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable12 Is Nothing Then
            MsgBox "Variable12 header was not found."
            Exit Sub
        End If
        Range(rngVariable12, rngVariable12).End(xlDown).Copy
             Range(rngVariable12, rngVariable12).End(xlDown).Offset(1, 0).PasteSpecial
        
    Dim rngVariable17 As Range
        Set rngVariable17 = Range("A2:AZ2").Find("Variable17", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable17 Is Nothing Then
            MsgBox "Variable17 header was not found."
            Exit Sub
        End If
        Range(rngVariable17, rngVariable17).End(xlDown).Copy
             Range(rngVariable17, rngVariable17).End(xlDown).Offset(1, 0).PasteSpecial

    Dim rngVariable19 As Range
        Set rngVariable19 = Range("A2:AZ2").Find("Variable19", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable19 Is Nothing Then
            MsgBox "Variable19 header was not found."
            Exit Sub
        End If
        Range(rngVariable19, rngVariable19).End(xlDown).Copy
             Range(rngVariable19, rngVariable19).End(xlDown).Offset(1, 0).PasteSpecial
        
    Dim rngVariable20 As Range
        Set rngVariable20 = Range("A2:AZ2").Find("Variable20", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable20 Is Nothing Then
            MsgBox "Variable20 header was not found."
            Exit Sub
        End If
        Range(rngVariable20, rngVariable20).End(xlDown).Copy
             Range(rngVariable20, rngVariable20).End(xlDown).Offset(1, 0).PasteSpecial

    Dim rngVariable23 As Range
        Set rngVariable23 = Range("A2:AZ2").Find("Variable23", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable23 Is Nothing Then
            MsgBox "Variable23 header was not found."
            Exit Sub
        End If
        Range(rngVariable23, rngVariable23).End(xlDown).Copy
             Range(rngVariable23, rngVariable23).End(xlDown).Offset(1, 0).PasteSpecial

    Dim rngVariable26 As Range
        Set rngVariable26 = Range("A2:AZ2").Find("Variable26", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable26 Is Nothing Then
            MsgBox "Variable26 header was not found."
            Exit Sub
        End If
        Range(rngVariable26, rngVariable26).End(xlDown).Copy
             Range(rngVariable26, rngVariable26).End(xlDown).Offset(1, 0).PasteSpecial

    Dim rngVariable27 As Range
        Set rngVariable27 = Range("A2:AZ2").Find("Variable27", , xlValues, xlWhole, xlByRows, xlNext, False)
        If rngVariable27 Is Nothing Then
            MsgBox "Variable27 header was not found."
            Exit Sub
        End If
        Range(rngVariable27, rngVariable27).End(xlDown).Copy
             Range(rngVariable27, rngVariable27).End(xlDown).Offset(1, 0).PasteSpecial


Dim rngD As Range
    Set rngD = Range("A2:AZ2").Find("Variable1", , xlValues, xlWhole, xlByRows, xlNext, False)
    If rngD Is Nothing Then
        MsgBox "Variable1 header was not found."
        Exit Sub
    End If

Dim rngE As Range
    Set rngE = Range("A2:AZ2").Find("Variable2", , xlValues, xlWhole, xlByRows, xlNext, False)
    If rngE Is Nothing Then
        MsgBox "Variable2 header was not found."
        Exit Sub
    End If
    
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range(rngD, rngD.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active Stuff").Sort.SortFields.Add2 Key _
        :=Range(rngE, rngE.End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Active Stuff").Sort
        .SetRange Range("A2:AZ65536")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
                    
End Sub

After a lot of work I did not manage to condense what already existed but I did manage to make what I had written into something that is now dynamic as in, if the column headers change locations, the information will still be put in the appropriate location. I am posting this code for anyone that may have a similar issue. It is a bit clunky and I am more than sure it can be made more efficient by someone with more skill in coding, but it does work. I did run the code to make sure it still ran properly after changing the variable names etc. to keep information confidential.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top