温馨提示:本文翻译自stackoverflow.com,查看原文请点击:excel - How to use my defined last row as part of my range?

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

发布于 2020-03-27 11:11:02

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.

查看更多

查看更多

提问者
calayton3
被浏览
92
BruceWayne 2019-07-03 23:04

Per my comment, try this (added comments to help show what I changed, but you can remove them in your actual code):

Sub FillWeeknDay()
Dim ws as Worksheet
' Change the worksheet name below if needed. It should be the name of the sheet
' you want the code to run on/with
Set ws = ThisWorkbook.Worksheets("Sheet1")

' Good job using `Long`, because the number of rows can exceed 
' what an `Integer` can hold.  Note how I added `ws` before the Range object
' which makes sure that we're counting the `Cells()...` info on the correct page.
Dim lRow As Long 
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Change variable name, and make a String
Dim userWeek as String
userWeek = InputBox("What week is this OOR being made in?")
ws.Range("C2:C" & lRow).Value = userWeek 

Dim userDay As String
userDay = InputBox("Are you creating this report on Monday, Wednesday, or Friday?")
ws.Range("D2:D" & lRow).Value = userDay 

End Sub

我进行了更改,Day因为它是VBA中的保留术语。Week只是为了与userDay“样式” 相匹配而进行了更改...但是可能已经保留了Week

编辑:我还假定此代码在不是Personal.xlsb的工作簿中。如果您将其放在中Personal.xlsb,请更改ThisWorkbookActiveWorkbook

发布
问题

分享
好友

手机
浏览

扫码手机浏览