如何设置一个等于单元格地址的变量? [英] How to set a variable equal to a cell address?

问题描述

我必须在B列中找到下一个空单元格(变量coluna_amostras)，保存其地址(使用变量inserir)，然后使用它粘贴新的行数据.但是，我不知道如何将地址存储在inserir变量中.按照定义，excel将返回运行时错误91-对象变量或未设置块变量".有人可以帮我吗?谢谢！

I have to find the next empty cell in column B (variable coluna_amostras), save its address (using variable inserir) and use it to paste a new row data. However, I couldn't figure out how to store the address in inserir variable. As it´s defined, excel returns "Run-time error 91 - Object variable or with block variable not set". Could someone help me? Thanks!!

    Sub CopiarOriginais()


    Dim Certeza As VbMsgBoxResult
    Dim sample As String
    Dim coluna_amostras As Range
    Dim inserir As Range


        ActiveSheet.Name = Range("Y1").Value
        sample = Range("Y1").Value

    Certeza = MsgBox("Você tem certeza de que os dados originais já não foram copiado? Utilizar novamente essa função, após o teste 2-sigma ter sido aplicado, comprometerá os seus dados originais.", vbYesNo)

        If Certeza = vbNo Then End

        Sheets("Results").Activate
        Range("B2").End(xlDown).Offset(1, 0).Select
        inserir = ActiveCell

  Sheets(sample).Activate

        Range("B3:D122").Copy
        Range("B132").PasteSpecial xlPasteValues
        Application.CutCopyMode = False


        Worksheets(sample).Range("ratio143144").Copy

            Worksheets("Results").Activate
            Range("D" & inserir.Row).Select
            ActiveSheet.PasteSpecial Link:=True

推荐答案

inserir = ActiveCell 与 inserir.Value = ActiveCell.Value 相同，由于 inserir 是 Nothing .

如果要保存对对象的引用，则必须使用 Set :

Set inserir = ActiveCell

这篇关于如何设置一个等于单元格地址的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！