在FBA中为FillDown动态设置范围 [英] Dynamically Set Range in VBA for FillDown

问题描述

Dim LastRow As Long, AddText As Long, SelectText As String
LastRowL = Range("L65536").End(xlUp).Row
AddText = LastRowL + 1
SelectText = "L" & AddText
Range(SelectText).Value = "Shipped"
Range("L2:L" & Range("A65536").End(xlUp).Row).FillDown 'How can I dynamically change the start range here? I want to start at SelectText.

感谢您帮助

Thank you for helping

推荐答案

我不确定是什么尝试实现，但上面的代码没有适当的上下文。这意味着什么？

范围对象 [ ^ ]表示一个单元格，一列，一个在当前活动的工作表中行和选择单元格。



请执行以下操作：

I'm not sure what are trying to achieve, but above code hasn't got proper context. What it means?
Range Object[^] represents a cell, a column, a row and selection of cells in currently active Sheet.

Please, do the following:

1. 激活 Sheet1
2. 转到代码窗格（ALT + F11）
3. 插入新模块
4. 复制并粘贴下面的代码

Sub TestRange()
Range("A1") = "Test no. " & Rand()
End Sub

运行代码（F5）

转到活动工作表（ Sheet1 ）并检查A1单元格值

现在，转到另一张表（ Sheet2 ）并再次运行宏

检查Sheet1和Sheet2中的A1单元格

Run the code (F5)

Go to active sheet (Sheet1) and check A1 cell value

Now, go to another sheet (Sheet2) and run macro again

Check A1 cells in Sheet1 and Sheet2

结论：在使用上下文中编写代码，例如：

Conclusion: Write the code on context of usage, for example:

Sheet1.Range("A1") = Rand()
'or (better)
Worksheets("Sheet1").Range("A1") =Rand()
'or (best of)
ThisWorkbook.Worksheets("Sheet1").Range("A1") =Rand()

让我们回到你的问题...

我建议你编写自定义函数：

Let get back to your question...
I would suggest you to write custom function:

Function GetFirstEmptyRow(wsh As Worksheet, Optional sColName as String= "A") As Long
GetFirstEmptyRow = wsh.Range(sColName & wsh.Rows.Count).End(xlUp).Row +1
End Function

用法：

Usage:

Dim myWsh as Worksheet
Dim firstEmptyRow As Long
Set myWsh  = ThisWorkbook.Worksheets("Sheet1")
firstEmptyRow  = GetFirstEmptyRow(myWsh, "L")
myWsh.Range("L" & firstEmptyRow).Select
Set myWsh = Nothing

这篇关于在FBA中为FillDown动态设置范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！