I Need Help

[cpanetta01]

Thank you in advance

I need to rite code such that when the button is pressed the next integer is appended to the end of thetable and its square (n^2) and root (SQR) are calculated and displayed in the table.

here is what it should look like https://gyazo.com/79ad9088bdf3893edc4fa0165df1c2b5

This is the code i have so far

Columns("B:C").ColumnWidth = 22


range("a1").Value = "Number"
range("b1").Value = "Square of the Number"
range("c1").Value = "Square root of the Number"


range("a2:c2").Value = 1

I need to use "Range("...").End(...).activate and Range("...").Offset(...).<wbr style="white-space: pre-wrap;">Activate" but i have no idea how to implement them.

Thank you,

 

[Trebor76]

Hi cpanetta01,

Welcome to MrExcel!!

Can I ask what you need this for? It seems like a homework assignment which while we do not supply answers for, we can give some pointers to get you started. If it's work related I can write some code though you can achieve this via formulas quite easily.

Thanks,

Robert

 

[cpanetta01]

Yes, this is a homework assignment however, I’m not looking for the full code I just need help getting started and I’ll try and figure out the rest.

Thank you

 

[alansidman]

OOps. Didn't realize it was homework. Deleted.

You will have to look into using the SQR function as well as testing to determine if the cell A1 is already populated and then run your calculations. You will need to determine the last row and then populate the next row based upon that information.

 

[Trebor76]

Thanks for your honesty

I would create the three worksheet formulas first (even the next number can be a formula based on the row number), then find code that finds the last row across columns A to C and increment it by one assign it to a long variable and use this row number to insert the the three worksheet formulas.

Have a go and if you get really stuck post back with what you have and we'll have a look.

Thanks,

Robert

 

[cpanetta01]

this is what i came up with

For x = 2 To 16
Cells(x, 1).Interior.Color = RGB(178, 178, 178)
Cells(x, 1).Value = (x - 2) + 1
Cells(x, 2).Value = ((x - 2) + 1) ^ 2
Cells(x, 3).Value = ((x - 2) + 1) ^ (1 / 2)
Next x

unfortunately this does the whole thing in one click and not separately

i still dont know how to incorporate "Range("...").End(...).activate and Range("...").Offset(...).<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250); white-space: pre-wrap;">Activate"

 

[Trebor76]

So following my suggestion you could have used Excel's POWER and SQRT functions (of which I believe there are @ 470 in total) to work out the values but knowing the actual formula is even better.
So now you only want to populate the next row available beneath columns A to C so there's no need to loop like your code is doing. In the following I have written code to do this and assigned it to a variable (I've also annotated the code to help explain what it's doing). See if you can use this to return the desired values from your code above into columns A, B and C:

Option Explicit
Sub Macro1()
    
    '//Define variable//
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False 'Turn screen updating off for more efficient processing
    
    'Set variable to be last row found across columns A to C and increment this by one
    On Error Resume Next 'If there's no data on the current (active) tab ignore error as we'll set the variable manually if it's zero
        lngMyRow = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If lngMyRow = 0 Then
            lngMyRow = 2 'Default row number if there's no data on the current (active) tab. Change to suit if necessary.
        Else
            lngMyRow = lngMyRow + 1 'Increment the last row number found across columns A to C by one
        End If
    On Error GoTo 0 'Nullify error message suppression

    Application.ScreenUpdating = True 'Turn screen updating back on
    
End Sub

Robert