CODEWITHSUNDEEP
vbaexcelreferencerangeselection
Blake Alexander
Blake Alexander

Reputation: 27

Selecting last row, range, vba

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.

Upvotes: 0

Views: 1477

Answers (3)

BerticusMaximus
BerticusMaximus

Reputation: 725

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

Upvotes: 0

jellz77
jellz77

Reputation: 354

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

Upvotes: 0

Dave
Dave

Reputation: 4356

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.

Upvotes: 1

Related Questions