Need my macro to automatically select next row

[student111]

Hey All,

So currently, i have a macro that requests a set of data, which it will then automatically log into a table, and initiate a few other actions. What i need however is, when the macro is run again, it will enter the new data set in the appropriate columns on the next row down, rather than rewrite the previous data. i.e, run macro once, it will fill up d1, e1, f1. When i run it again, i need it to fill d2,e2,f2. To note, there are other bits of data above and below this table, so that complicates it i think.

Anyway, if anyone could help me with this i would be really grateful!

here is the code:

 Sub TestMacro()    
    Range("B" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Enter Name of Point:")
    Range("c" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Enter Northing Coordinate:")
    Range("d" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Enter Easting/Westing Coordinate:")
    Range("e" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Enter Transition Length (Ls):")
    Range("f" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Minimum Radius (Rc)")
    Range("g" & "12").Select
    ActiveCell.FormulaR1C1 = InputBox("Enter Central Angle (DELTA):")
    Range("u" & "13").Select
    ActiveCell.FormulaR1C1 = Range("B160")
    Range("v" & "13").Select
    ActiveCell.FormulaR1C1 = Range("C160")
    Range("w" & "13").Select
    ActiveCell.FormulaR1C1 = Range("D160")
    Range("x" & "13").Select
    ActiveCell.FormulaR1C1 = Range("E160")
   
End Sub

 

[wrightyrx7]

Does the data start in row 12?

Also the second half of your code is in row 13, is this correct? is it not meant to be on the same row as the data you have input?

 

[nigelk]

..

 

[wrightyrx7]

Without you replying im guessing the ROW 13 part is static.

Also a guess that you have =SUM(B2:B159) in B160, the same for C,D and E

Option Explicit
Sub TestMacro()
Dim lRow As Long


With ActiveSheet
 lRow = .Range("B159").End(xlUp1).Row + 1
    .Range("B" & lRow).Value = InputBox("Enter Name of Point:")
    .Range("C" & lRow).Value = InputBox("Enter Northing Coordinate:")
    .Range("D" & lRow).Value = InputBox("Enter Easting/Westing Coordinate:")
    .Range("E" & lRow).Value = InputBox("Enter Transition Length (Ls):")
    .Range("F" & lRow).Value = InputBox("Minimum Radius (Rc)")
    .Range("G" & lRow).Value = InputBox("Enter Central Angle (DELTA):")
    .Range("U13").Formula = "=B160"
    .Range("V13").Formula = "=C160"
    .Range("W13").Formula = "=D160"
    .Range("X13").Formula = "=E160"
End With
End Sub

 

[student111]

Thanks for the reply!

Yep, data start on row 12, and the second half relates to a separate table. Im getting a problem, whereby the variable 'xlUp1' is not defined.

 

[student111]

or maybe it's refering to 'Sub TestMacro()'- im useless with this..

 

[wrightyrx7]

oops... take the '1' off the end. DOnt know how that got there haha

xlUp

Whats the first and last row in the table for this code?

B12 to B??

 

[student111]

b12 to b89

 

[wrightyrx7]

Try

Sub TestMacro()
Dim Rng As Range
Dim lrow As Long
    With ActiveSheet
        Set Rng = .Range("B12:B89")


        lrow = Rng.SpecialCells(xlCellTypeBlanks).Cells(1, 1).Row
            .Range("B" & lrow).Value = InputBox("Enter Name of Point:")
            .Range("C" & lrow).Value = InputBox("Enter Northing Coordinate:")
            .Range("D" & lrow).Value = InputBox("Enter Easting/Westing Coordinate:")
            .Range("E" & lrow).Value = InputBox("Enter Transition Length (Ls):")
            .Range("F" & lrow).Value = InputBox("Minimum Radius (Rc)")
            .Range("G" & lrow).Value = InputBox("Enter Central Angle (DELTA):")
            .Range("U13").Formula = "=B160"
            .Range("V13").Formula = "=C160"
            .Range("W13").Formula = "=D160"
            .Range("X13").Formula = "=E160"
    End With
End Sub

 

[student111]

Haha, i just appreciate the help mate! I don't have access to the file again until monday unfortunately, but ill give it a go then and get back to you.