excel - 如何将定义的最后一行用作范围的一部分？

I am currently an intern for the summer, brand-new to VBA (just picked it up on 6/29/19), and I am attempting to create an Order Aging Report for visibility at my company. The problem in question is that no matter how many variations on the code I try (I will attach a few examples), I either encounter a type mismatch error, or the code runs without problems but does not enter the calculated values into the cells in that range. I wouldn't ask if the resources I've already tried had solved the problem, and I'm prepared to recognize that its most likely a pretty foundational error. I haven't found any posts on StackOverflow that solve my particular problem. I am simply very new to this and coding in general (I just picked up Python a few months ago for Data Science). Would someone be willing to help me with direction (not a solution) toward the proper solution? It feels like a very simple solution and I'm kicking myself because I still can't figure it out.

• I've tried converting last row value to a string in order for it to be compatible with the range that I'm trying to concatenate it with, this only produces a type mismatch error.

• I've tried the concatenate method with this format: Range("C2" & LR), and this is where the problem of the code running but returning no values comes in.

• I've tried defining a counter for the rows and then printing the value of that to a cell outside my table and using that as part of the calculation.

• I've tried a For Each loop in each column and used the values entered into the Input Boxes as the values entered for each cell. This worked, but it just froze my Excel every time it ran.

• I've tried using an arbitrarily chosen number that is larger than the table, and this worked. But for obvious reasons this isn't the solution I want. It's just lazy and provides no learning value haha.

I hope this background isn't too limiting, and I want to thank everyone in advance for the advice!

Here's the current code I'm running:

    Sub FillWeeknDay()

    Dim lRow As Long
    Dim LR As String
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    LR = lRow

    Dim Week As Variant
    Week = InputBox("What week is this OOR being made in?")
    Range("C2:C" & LR).value = Week

    Dim Day As Variant
    Day = InputBox("Are you creating this report on Monday, Wednesday, or Friday?")
    Range("D2:D" & LR).value = Day

    End Sub

The expected result is that the values entered into the input boxes would be entered into the cells in the Range up until the last row in that column. The actual result is either nothing at all or the type mismatch error that I mentioned.

查看更多