使用另一个单元格值来引用excel VBA中的范围

I have a work sheet that has some data in it. To simplify adding it into an email, I've created some code that upon a button press would select a range of cells and save it as an image to upload into email. The current code is the following:

我有一张工作表,里面有一些数据。为了简化将其添加到电子邮件中,我创建了一些代码,按下按钮会选择一系列单元格并将其保存为图像以上传到电子邮件中。目前的代码如下:

Sub Button3_Click()
'
' Button3_Click Macro
'

'
Range("A1:D43").Select
ActiveWindow.SmallScroll Down:=-39
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
End Sub

I want to use a different cell to reference the range. So for example in cell J1 I would insert "A1", and in sell K1 I would insert "D43" to find my range so the range could be updated without having to edit the code. Is this possible? Thanks!

我想使用不同的单元格来引用该范围。因此,例如在单元格J1中,我将插入“A1”,在卖出K1中,我将插入“D43”以查找我的范围,因此可以更新范围而无需编辑代码。这可能吗?谢谢!

3 个解决方案

#1

Welcome to * :)

欢迎来到* :)

A1 can be written as Range("J1").Value and D43 can be written as Range("K1").Value. So "A1:D43" can be written as Range("J1").Value & ":" & Range("K1").Value

A1可写为Range(“J1”)。值和D43可写为Range(“K1”)。值。因此“A1:D43”可写为Range(“J1”)。值和“:”和范围(“K1”)。值

Simply replace Range("A1:D43").Select with Range(Range("J1").Value & ":" & Range("K1").Value).Select

只需更换范围(“A1:D43”)。选择范围(范围(“J1”)。值和“:”和范围(“K1”)。值)。选择

BTW, avoid using the .Select. You may want to see How to avoid using Select in Excel VBA

顺便说一句,避免使用.Select。您可能希望查看如何避免在Excel VBA中使用“选择”

Your code can be written as

您的代码可以写成

Range(Range("J1").Value & ":" & Range("K1").Value).CopyPicture _
Appearance:=xlScreen, Format:=xlBitmap

I am assuming that Range("J1") and Range("K1") are in the same sheet as from where you want to make the selection. If not then fully qualify the cells. For Example

我假设Range(“J1”)和Range(“K1”)与您想要进行选择的工作表位于同一工作表中。如果没有那么完全限定细胞。例如

Sheet1.Range(Sheet2.Range("J1").Value & ":" & Sheet2.Range("K1").Value)

Change Sheet1 and Sheet2 as applicable.

根据情况更改Sheet1和Sheet2。

As jeeped showed in his post, you can also write the above as

正如jeeped在他的帖子中所示,你也可以将上面的内容写成

Sheet1.Range(Sheet2.Range("J1").Value, Sheet2.Range("K1").Value).CopyPicture _
Appearance:=xlScreen, Format:=xlBitmap

#2

try,

Sub Button3_Click()
'
' Button3_Click Macro
'
    Range(cells(1, "J").text, cells(1, "K").text).CopyPicture _
       Appearance:=xlScreen, Format:=xlBitmap

End Sub

#3

another possibility, using Join() function on an array made out of wanted range values:

另一种可能性,在由想要的范围值构成的数组上使用Join()函数:

Option Explicit

Sub Button3_Click()
'
' Button3_Click Macro
'
    Range(Join(Application.Transpose(Application.Transpose(Range("J1:K1").Value)), ":")).CopyPicture _
    Appearance:=xlScreen, Format:=xlBitmap

End Sub

#1