Selecting last row, range, vba

0
votes

I'm fairly new to vba and have a rather simple problem. Can someone please help:

Instead of selecting the specific cell, I want my vba macro to go to the bottom of the column of interest, skip, and two cells below it do the following:

    Range("W72").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Null_value"
    Range("X72").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "=R[-2]C[1]-SUM(R[-2]C[-8]:R[-2]C[-6])"

As you can see the code above refers to the specific cells W72 and X72. Currently, the last entry in these columns are in W70 and X70 but next month my dataset will get bigger so W72 and X72 aren't the right locations to do the actions above. How do I correct for this such that my vba code is automatically going to the bottom of W(n):X(n), skips one row and in W(n+2), X(n+2) performs the code above.

Also, my formula above (ActiveCell.FormulaR1C1) also is referring to specific cells, in my case Row 70 several columns to the left, but as you probably tell, this too has the same issue since the row referencing changes each month. I need to get my vba to have the formula pick up the last row of those columns, the columns are P,Q,R.

Thanks for any help you can provide.

Update: Part of my same working project, I would greatly appreciate if anyone can help with this too. Thank you:

Hi All,

I currently have an input box for a variable that changes everymonth:

r_mo = Application.InputBox(prompt:="Enter the reporting month as YYYYMM (Eg:201604). Errors in this entry will result in errors in the results.")

This prompts an input box which one has to manually enter into... However, I want to automate this process and eliminate the need for an input box. Isn't there a now function in vba that will automatically generate today's date.

From a now, or system function all I want to do is extract the year in four digits and the month in two digits.

So for example, if we're in decemeber 2016

Sub asasdas ()

"Now function" r_mo = YYYYMM ' automatically updated from "now function"

End Sub

I appreciate any help you can give me and thank you so much all.

3
vbaexcelreferencerangeselection
Dave has given you a good answer below. An additional tip however, which will stand you in good stead in VBA: it is almost never necessary to select cells with the code. Instead of doing "Range(...).Select", and then "Selection.NumberFormat=..." - you can simply say "Range(...).NumberFormat=...". You will learn how to use "Select" from viewing the output of the macro recorder. That's how everyone learns :) But the next thing they learn is that when you're writing code yourself, you can just perform the relevant operations directly on the cells without selecting. Good luck! - Chris Melville

3 Answers

1
votes

You can get the last populated row of a given column (W in my example) in VBA with the following code:

Dim ws As Worksheet : Set ws = ThisWorkbook.Worksheets("MySheetNameHere")    
lastRow = ws.Cells(ws.Rows.Count, "W").End(xlUp).Row

Naturally, if you then add 2 to lastRow you have the cell you are looking for.

0
votes

I'd do it like 

Sub asdf()

    Range("w1048576").End(xlUp).Offset(2, 0).Select 'gets the last row
    With Selection
        .NumberFormat = "General"
        .FormulaR1C1 = "Null_value"
    End With

    ActiveCell.Offset(, 1).Select

    With Selection
        .NumberFormat = "General"
        .FormulaR1C1 = "=R[-2]C[1]-SUM(R[-2]C[-8]:R[-2]C[-6])"
    End With

End Sub
0
votes

If you want a more detailed answer you're going to have to make a new question but for your second question try this.

Sub Now()
    Dim myDate As String
    myDate = Date
    myDate = Format(myDate, "yyyymm")
    Debug.Print myDate '201606 output for June 10th 2016
End Sub